Forum Discussion
DATESMTD model does not work on generic date table
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:
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 :
:
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:
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!
3 Replies
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/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYMEcalculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/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
It is now solved. I did not change the approach, simply instead of MAX in the calendar table, I used TODAY.
