Get the Last Date from each "monthyear" which is a Textformat

[darko_ivanovic]

Hi Community!

I pretty new to DAX so i hope someone can help me with this one.

This is my "Date Table" in my datacube. I am using the "Month-Year" as a SELECTEDVALUE in powerbi. and i need to compare the last date of each month-year to something. The Problem is that the Column "Month-Year" is of type TEXT so i can't use the ENDOFMONTH(date) function. Above the table you see the DAX query I used to create this Table. I hope there is a way with a DAX query to get somehow the Last date of each "Month-Year". 

[v-xicai]

Hi  @darko_ivanovic ,

Does that make sense? If so, kindly mark the right reply as a solution to help others having the similar issue and close the case. If not,  let me know and I'll try to help you further.

Best regards

Amy

[Ashish_Mathur]

Hi,

I suggest you create another calculated column formula to extract the Month =FORMAT('Date Table'[Date],"mmmm").  Then create 2 slicers - one each for Year and Month and select any one month and Year.  Then write this measure

=MAX('Date Table'[Date])

Hope this helps.

[v-xicai]

Hi @darko_ivanovic ,

The ENDOFMONTH function returns the last date of the month in the current context for the specified column of dates.

You may use the "Date" column instead of "Month-Year" column, then codes look like “ENDOFMONTH([Date]) ” .

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.