cancel
Showing results 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

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

Thanks,

Krish..

1 ACCEPTED SOLUTION
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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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...
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...

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

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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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...
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...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors