cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
userdata
Helper IV
Helper IV

how to show previous month total in a card without any date slicers

Hi, 

I tried to create a card that should automatically show the sum of total in the previous month. I have created the measure that works in the table and when i put date or month in there but when there is no filter at all and i just want to show automatically every month the card with the previous month sold, it gives me blank?

 

 

I attached the file.

https://github.com/userdata21/file/blob/master/card_not%20showing_previousmonth.pbix

 

Many thanks!Capture11.JPG

1 ACCEPTED SOLUTION

@userdata I think you want this measure:

 

PrevMonth = 
IF (
    ISINSCOPE ( 'Date'[Month] ),
    CALCULATE (
        TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
        PREVIOUSMONTH ( 'Date'[Date] )
    ),
    LASTNONBLANKVALUE (
        'Date'[Month],
        CALCULATE (
            TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
            PREVIOUSMONTH ( 'Date'[Date] )
        )
    )
)

 

prevmonth.JPG

 

Is that what you are going for?

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@userdata , a previous month can from time intelligence need a date. And when we use a calendar, it takes the end date to the calendar if no date is given.  So stop your calendar at today or eomonth of today

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]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

 

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak  Thanks for that. I tried it previous month and dateadd but the problem is that i cant say use today because in my date from sold table there is not date for today but the data goes only until end of last month.  When i tried using today, it gave me blank and i want it in a card where it automatically changes based on last month?

Anyone ideas how to show the last month in the card when date for sold just has data until last month. I use relative date filtering and change it to last month, to show the last month in the card but how can I do this in Dax without using the relative date filtering to filter last month?

@userdata I think you want this measure:

 

PrevMonth = 
IF (
    ISINSCOPE ( 'Date'[Month] ),
    CALCULATE (
        TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
        PREVIOUSMONTH ( 'Date'[Date] )
    ),
    LASTNONBLANKVALUE (
        'Date'[Month],
        CALCULATE (
            TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
            PREVIOUSMONTH ( 'Date'[Date] )
        )
    )
)

 

prevmonth.JPG

 

Is that what you are going for?

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

@DataZoe omg thanks sooo much! Youre a herooo! Yes that was what I was looking for. Can you maybe explain the logic how the Dax works to understand the steps? Maaaaany thaaaanks @datazoe!!

@userdata of course! I'm glad it was what you were looking for 🙂

 

First, the measure checks to see if it's being filtered down to a specific month with the ISINSCOPE( 'Date'[Month]).

  • If it's being filtered down, then the TOTALMTD() with PREVIOUSMONTH() will just work by taking the last month of the filtered month's value. So if month 8 is given, it will show month 7, etc.
  • If it's NOT being filtered down to a specific month (multiple months selected, like in the total or a card), then we utilize LASTNOTBLANKVALUE() which says hey, lets just pretend we are the latest month in the date range and do the calculation please.

 

PrevMonth = 
IF (
    ISINSCOPE ( 'Date'[Month] ),
    CALCULATE (
        TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
        PREVIOUSMONTH ( 'Date'[Date] )
    ),
    LASTNONBLANKVALUE (
        'Date'[Month],
        CALCULATE (
            TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
            PREVIOUSMONTH ( 'Date'[Date] )
        )
    )
)

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors