No of days

[#1]

Good morning guys,

I have made a column with the expression DAY(MONTHEND(MAKEDATE(PostingYear,PostingMonth))) to show the number of days in a month as shown in the image below

This works fine until I selected say 2013, the Days column only shows the days in 2013 only as shown in the image below

And adds up to the weirdness to it is that, say I select 2013 - May, it will on show the days for May 2013 only as show in the image below

For you info,

• the Revenue column expression is --

         SUM({<PostingYear=, PostingMonth=, EndPostingDate=, PostingDateNum={"<=$(=Max(PostingDateNum))"}>}
    RevAmount)/1000

• the Days column expression is --

       DAY(MONTHEND(MAKEDATE(PostingYear,PostingMonth)))

• the AR YTD expression is --

       SUM({<PostingYear=, PostingMonth=, EndPostingDate=, PostingDateNum={"<=$(=Max(PostingDateNum))"}>}
    ARAmountYTD)/1000     

Can somebody point out what I'm missing in my Days expression?

Thank you so much for your kind attention guys

[SergeyMak · Accepted solution]

If you have Calendar as dimensional table (it's the best practice) you can calculate the number of days in this table and use

Only({$<Calendar.Month=,Calendar.Year=>}NoOfDays)

or just

For No of days it's better

Count({$<Calendar.Month=,Calendar.Year=>}Distinct Calendar.Date)

because you need to clear your year and month selection in set analysis for the expression

[#3]

Oh my why I didn't think of that. Thank you very much bro!