Forum Discussion
Add last month closed flag in DAX or Power Query
Hi everyone !
I need to show the data up to the last closed month in my report.
Example: today is 10/15 (October is an open month) in this case I need to view all the data for the year up to 09/30 (last closed month).
Could you create this flag in DAX or Power Query?
Thank you!
Regards!
You can add this column to your Date table and then filter on "Y" to see only closed months.
Closed Month = if(EOMONTH('Date'[Date],0) <= TODAY(), "Y", "N")Regards,Pat
4 Replies
- amitchandak
Super User
Anonymous , You can use YTD with date table and force it stop at last month
LYMTD QTY forced=
var _dt = eomonth(today(),-1) // Or // maxx(allselected(Date),Date[Date])
returnif('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi amitchandak , thanks for yo answer.
My idea is to add a flag in a page filter, but not for a measure. I wanted to know if it is possible to define this in my calendar dimension either in DAX or Power Query.
Regards!
- mahoneypat
Microsoft Employee
You can add this column to your Date table and then filter on "Y" to see only closed months.
Closed Month = if(EOMONTH('Date'[Date],0) <= TODAY(), "Y", "N")Regards,Pat
