Calculate total of most recent month or at least of previos month compared to today

[Anonymous]

Hello,

I have a Dataset named PA with two columns: Date, and Total Sales. Right now, I have a measure that filters based the following:

FILTER(PA,MONTH(PA[Date Snapshot])= MONTH(TODAY())))

This calculates total of the current month. However, if my data is not updated until the 5th of the month, let's say, this measure will not show anything. because there is not gonna be any data in the current month. Therefore, the best scenario is to create a measure that calculates the sales for the most recent month in that column.  Alternatively, a less ideal but fine solution would be to always show sales from previous month. I am also not sure how to do this, since I cannot just pass TODAY() to PREVIOUSMONTH.

Thanks for your help!

[amitchandak]

@Anonymous , see if you can use time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
LMTD= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

LMTD -2= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
previous Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
previous (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

Now

if(isblank([MTD]), [LMTD],[MTD])

same way for last month

if(isblank([MTD]), [LMTD-2],[LMTD])

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

@Anonymous Maybe try:

Measure =
  VAR __Calc = <your calc>
RETURN
  IF(ISBLANK(__Calc),<your calc with [Date Snapshot] = MONTH(TODAY())-1>
?

Can be more specific with sample data. 

[Anonymous]

hi @Greg_Deckler,

Thank you for your answer. I tried using what you wrote and I got the final code below. Unfortunately, it shows a Blank Value.

TEST =

VAR __Calc = calculate(

       SUM(PA[Sales Amount])/SUM(PA[Split per Year 2021]),FILTER(PA,MONTH(PA[Date Snapshot])= MONTH(TODAY())))

RETURN

       IF(ISBLANK(__Calc),calculate(SUM(PA[Sales Amount])/SUM(PA[Split per Year 2021]),FILTER(PA,MONTH(PA[Date Snapshot])= MONTH(TODAY()-1))))

Basically, I want to calculate the ratio of sales amount/ Split per year 2021.

If I just the next line, it works fine, but it is only able to calculate current month:

Mewasure  = calculate(

SUM(PA[Sales Amount])/SUM(PA[Split per Year 2021]),FILTER(PA,MONTH(PA[Date Snapshot])= MONTH(TODAY())))

Any idea what is wrong? Thanks!

[Anonymous]

HI @Anonymous,

Did Greg_Deckler works on your side? If not, please share some dummy data then we can test to coding formula on them.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Greg_Deckler · Accepted solution]

@Anonymous Are you missing the second part of your IF statement? May be easier to see here, use daxformatter.com:

TEST =
VAR __Calc =
    CALCULATE (
        SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
        FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () ) )
    )
RETURN
    IF (
        ISBLANK ( __Calc ),
        CALCULATE (
            SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
            FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () - 1 ) )
        ), __Calc
    )