Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need help with the following: I have data that looks like this -
Date of Purchase Product Value
Dec 31, 2022 A
Jan 31, 2023 A
Feb 28, 2023 A
Dec 31, 2022 B
Jan 31, 2023 B
Dec 31, 2022 C
Dec 31, 2022 D
Dec 31, 2022 E
Jan 31, 2023 E
Feb 28, 2023 E
I need to create a report (with a measure?) that will return two things:
- The total value for products A, B, C, D, E on Dec 31, 2022
- The latest total available value for all products, so for the above that would be the following -
Feb 28, 2023 A
Jan 31, 2023 B
Dec 31, 2022 C
Dec 31, 2022 D
Feb 28, 2023 E
Is there any way to do this without writing complicated DAX? I am fairly new to Power BI.
Thank you!
Solved! Go to Solution.
Hi @LadyCat,
#1, You can try to use the following measure formula to summary values based on specific date.
Total for the last day of previous year=
VAR _lastdate =
MAXX ( ALLSELECTED ( table ), [Date] )
RETURN
CALCULATE (
SUM ( table[value] ),
FILTER (
ALLSELECTED ( table ),
[Date]
= DATE ( YEAR ( _lastdate ) - 1, 12, 31 )
)
)
#2, I think you can use measure filter to filter visual records.
For detail operation steps, you can write a measure formula to get the last date based on category and compare with current date to return flag. Then you can use it on visual level filter to filter records.
flag=
VAR currdate =
MAX ( table[Date] )
VAR _lastDate =
CALCULATE (
MAX ( table[Date] ),
ALLSELECTED ( table ),
VALUES ( table[Product] )
)
RETURN
IF ( currdate = _lastDate, "Y", "N" )
Regards,
Xiaoxin Sheng
Hi,
Ceate a Calendar Table with a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. To your visual, drag Product from the Data Table. Write these measures
Total = sum(Data[Value])
Total as on Dec 31 = calculate([Total],datesbetween(calendar[Date],date(2022,12,31),date(2022,12,31)))
Last date = max(Data[Date of Purchase])
Hope this helps.
Hi @LadyCat,
#1, You can try to use the following measure formula to summary values based on specific date.
Total for the last day of previous year=
VAR _lastdate =
MAXX ( ALLSELECTED ( table ), [Date] )
RETURN
CALCULATE (
SUM ( table[value] ),
FILTER (
ALLSELECTED ( table ),
[Date]
= DATE ( YEAR ( _lastdate ) - 1, 12, 31 )
)
)
#2, I think you can use measure filter to filter visual records.
For detail operation steps, you can write a measure formula to get the last date based on category and compare with current date to return flag. Then you can use it on visual level filter to filter records.
flag=
VAR currdate =
MAX ( table[Date] )
VAR _lastDate =
CALCULATE (
MAX ( table[Date] ),
ALLSELECTED ( table ),
VALUES ( table[Product] )
)
RETURN
IF ( currdate = _lastDate, "Y", "N" )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |