Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning,
Ultimately my goal is to calculate the number of transactions on the previous day excluding weekends and holidays but right now I can't even calculate the transactions of the previous day.
Measure 2 just shows no value. Measure 1 works correctly.
Can anyone help?
Thanks
Measure 1
AllOrders = DISTINCTCOUNT(Orders[Number])
Measure 2
PreviousDayOrders =
VAR PDate = LASTDATE('Calendar'[Date])
RETURN
CALCULATE ( [AllOrders],
FILTER ( ALL ( 'Calendar' ),
'Calendar'[Date] > PDate - 1 &&
'Calendar'[Date] >= PDate ) )
The Orders table is very simple
Order Created
1 | 2020-07-30 08:21:07 |
2 | 2020-07-29 08:15:07 |
3 | 2020-07-29 08:12:07 |
4 | 2020-07-30 08:21:072020-07-30 08:21:07 |
My Calendar table is simple as well. I have it marked as a date table.
Calendar = CALENDAR(MIN(Orders[Created]),MAX(Orders[Created]))
WeekDay = WEEKDAY('Calendar'[Date])
WeekDay / Weekend = IF ( WEEKDAY ( 'Calendar'[Date]) IN { 1, 7 }, "Weekend" , "Weekday" )
Number is datatype Whole number and it has no repeating values.
Created is datatype Date/Time and it has repeating values.
I found an error in my code, Measure 2 is now
well I figured it out. When I switched the date datatype from date / time to just date it started calculating properly.
@Anonymous
So have you figured out and reached your expected results? The following should also give you previous day value.
Measure =
var previousday = CALCULATE(MAX([Date]),FILTER(ALL('Table'),[Date]<MAX('Table'[Date])))
Return CALCULATE(DISTINCTCOUNT('Table'[Column 2]),FILTER(ALL('Table'),[Date]=previousday))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm getting pretty close. When I add this measure to a visualization and try to group by sales agent it gives the same number for each user.
I know this is by design because calculate ignores context but how do I fix it? I tried wrapping the calculate in a summarize but that didnt work. I can't find anything on this topic online.
Thanks.
@Anonymous , This blog can help
Traveling Across Workdays
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766
@amitchandak Thanks I will give that approach a try.
Currently I'm following this youtube video which is where my current approach is from - https://www.youtube.com/watch?v=B0pC1B4w5U0
@Anonymous , you formula would be very similar to last week
You have to use that working day rank.
You might have to choose non continuous Rank , one with null on weekend
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |