DATESMTD model does not work on generic date table

[Lena85]

Hello,

My model has 4 tables:

1.Table with business volume over time (reporting volume_over_time)

2. Generic date table (created on the dwh level called dwh dim_calendar_dates)  

3. DAX generated table with a calendar function

CALENDAR = CALENDAR(MIN('reporting volume_over_time'[waybill_created_date_ams]),MAX('reporting volume_over_time'[waybill_created_date_ams]))  

4. Date for date periods with the following DAX:

Date Perriods =

UNION(

ADDCOLUMNS(

DATESMTD('CALENDAR'[Date]),

"Type", "MTD","Order",1

),

ADDCOLUMNS(

DATESQTD('CALENDAR'[Date]),

"Type", "QTD", "Order",2

),

ADDCOLUMNS(

DATESYTD('CALENDAR'[Date]),

"Type", "YTD", "Order",3

),

ADDCOLUMNS(

PREVIOUSYEAR(DATESYTD('CALENDAR'[Date])),

"Type", "Last Year", "Order",6

),

ADDCOLUMNS(

PREVIOUSQUARTER(DATESQTD('CALENDAR'[Date])),

"Type", "Last Quater", "Order",5

),

ADDCOLUMNS(

PREVIOUSMONTH(DATESMTD('CALENDAR'[Date])),

"Type", "Last Month", "Order",4

)

)

The idea is to create custom period slicer for the volume, so that the end user can filter volume on ''Previous month/ Year/Quarter, current MTD, QTD, YTD, all with the use of one filter. Similar solution is shown here: https://www.youtube.com/watch?v=fKygF7VEJnQ 

This Period filter works well ONLY when it is based on the date column from table Calendar which references a date from the Reporting volume table : 

CALENDAR = CALENDAR(MIN('reporting volume_over_time'[waybill_created_date_ams]),MAX('reporting volume_over_time'[waybill_created_date_ams])) 

: 

However, I need this Date Period types to work on our dwh dim_calendar_dates generic date table, since the reporting volume weight has a multiple date references. Whenever I try to change the Calendar to be based on the dwh dim_calendar_dates, instead of reporting_volumes: 

CALENDAR = CALENDAR(MIN('dwh dim_calendar_dates'[date_id]),MAX('dwh dim_calendar_dates'[date_id])) 

then the Date in the Date Perriods table has only years 2023 and 2024, and somehow the type referencing last year's periods is connected to those future dates:

and none of the period types output any data:

Could anyone help me see where the issue is and how to fix it?

Thank you very much!

[Lena85]

It is now solved. I did not change the approach, simply instead of MAX in the calendar table, I used TODAY. 

[Lena85]

Hello

@amitchandak thank you for your proposal, but it seems to me that the measure slicer is not compatible as it works on tables with columns containing date:

However, what I need to build is card totals that are filterable through the use of YTD/MTD/QTD etc.. like that:

When I remove date as an object in your solution, the card visual does not work. Is there any work-around it or is this solution therefore not applicable to my use case? 

Thanks!

Lena

[amitchandak]

@Lena85 , I think you should just create table with MTD,QTD, YTD, This year etc in rows,

And then create measure using slicer value or you should use calculation group

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://www.youtube.com/watch?v=vlnx7QUVYME

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/