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

[userdata]

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!

[amitchandak]

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

[userdata]

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

[userdata]

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?

[DataZoe · 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] )
        )
    )
)

Is that what you are going for?

[userdata]

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

[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] )
        )
    )
)