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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
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.

 

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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