Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I am facing an issue in a matrix where I have to display MTD values.
I have created a calender table and joined with the date in the table.
We have data till 28-10-2022, when we select date as 31-10-2022 we are getting data in matrix as well as in Total
We don't have data after 28-10-2022, but when we select date as 30-11-2022, we are getting data in matrix as below
Ideally MTD data should not be visible in the matrix, it should display blank.
I have used below DAX for MTD values.
MTD = TotalMTD(Table(column),Date)
MTD = Calculate(sum(Table(column),DATESMTD(Date))
Could you please let mw know how to resolve the above issue. I want matrix to be displayed as below when we don't have data for MTD.
Thanks in advance.
@Optimusprime_25 , I am assuming date of your table is joined with the date of the date table and the formula of the measure is
Calculate(sum(Table(column),DATESMTD(Date[Date]))
Slicer should be on month from date table
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
@amitchandak Thanks for your response
Yes, we have joined date with date of date table. We have followed the same formula itself
Calculate(sum(Table(column),DATESMTD(Date[Date]))
But as per our report requirement we have slicer on Date, whichever the date user selects then formula should calculate MTD for that month of selected date.
Where we have no data, then it should display blank.
Thank you.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |