Get Delta - to show today and last month end difference

[Anonymous]

Hi @Anonymous @po  , need help with some DAX ,.
Requirement is to show delta between current day and last month last day closing sales data  .

i'm currently using the DateADD function this works fine as long as the long month last day falls on weekday if the last day falls on weekend , my delta shows me current day price which is incorrect .e.g. if i run a report for 31st july 2020 , i get the delta correctly since 30 jun falls on Tuesday , however if i run the report for 30 Jun 2020 ,i only get 30 jun data  as 31 may 2020 falls on sunday so it returns me (30 june - 0) = 30 jun data 

here is the DAX i'm using to get the last month price/last day price - this works fine but not when its a holiday .  =CALCULATE([LTP],DATEADD('Calendar'[Date],-1,MONTH)). 

I do have a date table connect to sales tables , and also weekends flagged .   appreciate your inputs 

[Anonymous]

THanks @v-yingjl  appreciate your time on this . 

[v-yingjl]

Hi @Anonymous ,

Based on your description, I have defined each Saturday and Sunday as holidays in my sample file. Create a measure like this:

Diff = 
VAR _today =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = TODAY () )
    )
VAR _lastmd =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        WEEKDAY ( LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) ), 2 ) = 6
            || WEEKDAY ( LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) ), 2 ) = 7,
        _today,
        _today - _lastmd
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  @v-yingjl 

Thanks for your response and appreciate the time taken to help on this . 

i did give this a try but was not successful . Attached is the .pbix FYR .

i created another simple table calles "TestTable" with month end prices for July/june/May , 

as said if i select the month of July , it works fine (31 july - 30 june) , gives me 0.20 but for 30 june .

the result i expect to see is 0.3 (1.7 - 1.4) . 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can modify the measure like this:

Diff = 
VAR _today =
    SUM ( 'Table'[Salesprice] )
VAR _lastmd =
    CALCULATE (
        SUM ( 'Table'[Salesprice] ),
        LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) )
    )
RETURN
    IF (
        WEEKDAY ( LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) ), 2 ) = 6
            || WEEKDAY ( LASTDATE ( PREVIOUSMONTH ( 'Table'[Date] ) ), 2 ) = 7,
        _today,
        _today - _lastmd
    )

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @Anonymous ,

Sorry for that seems could not see the .pbix file, could you please consider re-sharing it?

Best Regards,
Community Support Team _ Yingjie Li

[Anonymous]

@v-yingjl  Sorry i realised i can't attach the .pbix file , hence put in some screen shots .

as i'm using my organisation account can't give links to my OneDrive 

[amitchandak]

@Anonymous , You can create a flag in your date table for working day like

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

Last month last workday  =

measure =
var _max1 = today() //maxx(allselected(Date),Date[Date]) //Or selected Date

var _max = maxx(filter(Date, eomonth([Date],0) = emonth(_max1,-1) && [Work Day] =1),Date[Date])
return
calculate([sales], filter(all(Date), date[date] =max))

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.