Add last month closed flag in DAX or Power Query

[tutuk28]

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!

[amitchandak]

@tutuk28 , 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])
return

if('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.

[tutuk28]

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 · Accepted solution]

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

[tutuk28]

Thanks @mahoneypat  !