Forum Discussion
Helper IVLast Day Available for Each Month
In this recent post:
I got a solution to display the last day of each month. It turns out that not every month has a last date, as you can see for 2019-2020 May 2019 in this chart. How can I tweak the calculation to show the last day available for each month?
diogobraga2 for these kind of calcualtions usually you should use Date dimension by adding one in your model but for now you can achieve this by following
First add a column called STart of Month and then add anotehr column to flag Last Date of the month based on record
Start of month = EOMONTH( Dates[Date], -1 ) + 1 Is this last date of the month = VAR __lastDate = CALCULATE( MAX ( Dates[Date] ), ALLEXCEPT( Dates, Dates[Start of month] ) ) RETURN IF ( Dates[Date] = __lastDate , 1, 0 )
15 Replies
- parry2k
Super User
diogobraga2 for these kind of calcualtions usually you should use Date dimension by adding one in your model but for now you can achieve this by following
First add a column called STart of Month and then add anotehr column to flag Last Date of the month based on record
Start of month = EOMONTH( Dates[Date], -1 ) + 1 Is this last date of the month = VAR __lastDate = CALCULATE( MAX ( Dates[Date] ), ALLEXCEPT( Dates, Dates[Start of month] ) ) RETURN IF ( Dates[Date] = __lastDate , 1, 0 )
diogobraga2parry2k what do you mean by adding a date dimension? My model has a date table...
I am open to alternatives for the best way to display the monthly total of attending students. The dataset has a daily cumulative total, so I figured using the last day available per month was on point.
- parry2k
diogobraga2 Oh I never knew you have date table in your model, anyhow test what I proposed and see if you get what you are looking for.
- diogobraga2
parry2k I applied the formula you suggested (see below), but once again it returned the actual last days of the month, not the last day with data for that month. In the example I mentioned, May does not have data for days 29, 30, 31, therefore the calculation should return the data from May 28 as the last day of the month.
Is this last date of the month =VAR __lastDate =CALCULATE( MAX ([Date Adj]), ALLEXCEPT(student_status_count, student_status_count[Start of month]))RETURNIF ( [Date Adj] = __lastDate , 1, 0 )- parry2k
diogobraga2 did you added other column I mentioned Start of month
- mwegener
Most Valuable Professional
Hi diogobraga2
i think you should change the concept of your fact table to additive values (movements).
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
- diogobraga2
Thanks for you feedback mwegener , could you explain further how changing to 'additive values (movements)' works?
- mwegener
Hi diogobraga2 ,
I think you get cumulative values and want to show the last value per month.
But it would be better if you only received the changes and could calculate by summation the monthly value (annual value, etc.).
I tried to describe it here...
... and here nearly the same issue.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
