Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
HI,
I have data like this as below. For each date, I want to get the amount of the previous week where Active column is 'Yes'.
Date | Amount | Active | WeekRank |
28/01/2022 | 100 | Yes | 4 |
01/02/2022 | -50 | 5 |
How do I achieve that?
Hi @bml123 ,
1. I have created a calendar table with two columns, Date and WeekRank for slicer.
Calendar =
ADDCOLUMNS (
CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
"WeekRank", WEEKNUM ( [Date], 2 )
)
2. Create a measure to sum of the week.
Sum of last week =
VAR _week =
MAX ( 'Calendar'[WeekRank] ) - 1
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Active] = "Yes"
&& WEEKNUM ( 'Table'[Date], 2 ) = _week
)
)
Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @bml123
Try this:
Sample file attached.
Check this link for more details:
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @VahidDM
WeekRank is there in a separate calendar table and is not part of 'Table'.
How do I change the formula to reflect that?
Can you share a sample of your tables in a text format? and let us know the relationship between tables?
BTW, the easiest way is to use lookupvalue to add a WeekRank column to the main table.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |