Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
krrish116
Resolver II
Resolver II

How to Show Current Month data with out selection in Slicer.

Hi Team,
@amitchandak 

I had a data from 2022 to 2024 .

Invoice DateValue
Jan-22100
Feb-22200
Mar-22300
Apr-22100
May-22100
Jun-22100
Jul-22100
Aug-22200
Sep-22300
Oct-22100
Nov-22100
Dec-22100
Jan-23200
Feb-23300
Mar-23200
Apr-23300
May-23200
Jun-23300
Jul-23-16120
Jul-23200
Aug-23300
Sep-23100
Oct-23100
Nov-23100
Dec-23800
Jan-24300
Feb-24200
Mar-24300
Apr-24100
May-24100
Jun-24100
Jul-24100
Aug-24100
Sep-24100
Oct-24100
Nov-24200
Dec-24300

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 DateCurrent WeekCurrent Month ValuePrev. Month ValueProject Year Dec 23 ValueChange Last Month Vs Current MonthYear End Value Vs Current Month Value
2023-Jul-162023 -w28120300800180680

 

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..

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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...

View solution in original post

2 REPLIES 2
krrish116
Resolver II
Resolver II

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 DateCurrent WeekCurrent Month ValuePrev. Month ValueProject Year Dec 23 ValueChange Last Month Vs Current MonthYear End Value Vs Current Month Value
2023-Jul-162023 -w28120300800180680

 

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 DateCurrent WeekCurrent Month ValuePrev. Month ValueProject Year End ValueChange Last Month Vs Current MonthYear End Value Vs Current Month Value
2023-Jul-162023 -w2810010010000

 

The First two columns in table wont chnage rest all has to change.

Please tell me how to do it.

 

Thanks ,
Krish..

amitchandak
Super User
Super User

@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...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors