Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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) .
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.
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
@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
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |