March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Team,
@amitchandak
I had a data from 2022 to 2024 .
Invoice Date | Value |
Jan-22 | 100 |
Feb-22 | 200 |
Mar-22 | 300 |
Apr-22 | 100 |
May-22 | 100 |
Jun-22 | 100 |
Jul-22 | 100 |
Aug-22 | 200 |
Sep-22 | 300 |
Oct-22 | 100 |
Nov-22 | 100 |
Dec-22 | 100 |
Jan-23 | 200 |
Feb-23 | 300 |
Mar-23 | 200 |
Apr-23 | 300 |
May-23 | 200 |
Jun-23 | 300 |
Jul-23-16 | 120 |
Jul-23 | 200 |
Aug-23 | 300 |
Sep-23 | 100 |
Oct-23 | 100 |
Nov-23 | 100 |
Dec-23 | 800 |
Jan-24 | 300 |
Feb-24 | 200 |
Mar-24 | 300 |
Apr-24 | 100 |
May-24 | 100 |
Jun-24 | 100 |
Jul-24 | 100 |
Aug-24 | 100 |
Sep-24 | 100 |
Oct-24 | 100 |
Nov-24 | 200 |
Dec-24 | 300 |
We are Refreshing the Data on Every Sunday and on that day an additonal row is added to the data from backend and you can see it clearly in the table above.
Now we have to Show the present data in a Table visual like below where it contains
Refresh Date | Current Week | Current Month Value | Prev. Month Value | Project Year Dec 23 Value | Change Last Month Vs Current Month | Year End Value Vs Current Month Value |
2023-Jul-16 | 2023 -w28 | 120 | 300 | 800 | 180 | 680 |
Now the Problem is recently We have added a new slicer Year-Month to this Report and now if they select any year-month value like Jun-2022,
The values in table visual are showing Blank.
By default we have to show the recent refresh values and if we select any year month filter then it has to change values in above table visual.
Please help me to rectify this Issue.
Thanks,
Krish..
Solved! Go to Solution.
@krrish116 , Have date table with column like and save this on this month in slicer
Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)
create measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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.
for other refer
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
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
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
Hi @amitchandak ,
Now the Problem is recently We have added a new slicer Year-Month to this Report and now if they select any year-month value like Jun-2022,
The values in table visual are showing Blank.
By default we have to show the recent refresh values and if we select any year month filter then it has to change values in above table visual.
Refresh Date | Current Week | Current Month Value | Prev. Month Value | Project Year Dec 23 Value | Change Last Month Vs Current Month | Year End Value Vs Current Month Value |
2023-Jul-16 | 2023 -w28 | 120 | 300 | 800 | 180 | 680 |
For Example By default the values we have to shown like above.(i.e Based on LAst refresh date)
If I select any month like Nov-22 in the year month slicer.(Nov22 value 100, dec22 value 100 , oct22 value 100)
then it has to display.
Refresh Date | Current Week | Current Month Value | Prev. Month Value | Project Year End Value | Change Last Month Vs Current Month | Year End Value Vs Current Month Value |
2023-Jul-16 | 2023 -w28 | 100 | 100 | 100 | 0 | 0 |
The First two columns in table wont chnage rest all has to change.
Please tell me how to do it.
Thanks ,
Krish..
@krrish116 , Have date table with column like and save this on this month in slicer
Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)
create measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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.
for other refer
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
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
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
86 | |
84 | |
77 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |