Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This seems like it should be easy - but, I am a super newb to power bi. I was able to figure out accumulated total sales for the prior year period (filtered by page filter and slicer) but, when I try to use the same logic counting records (filtered by page filter and slicer), I get nowhere. This measure returns data I need for prior year, but it's not accumulated:
When I try to sum them up like this:
RecordID PY Accumulated =
CALCULATE([RecordID PY],(FILTER(ALLSELECTED('Calendar Master From Sharepoint Raw Datasets'), 'Calendar Master From Sharepoint Raw Datasets'[Date] <= MAX('Calendar Master From Sharepoint Raw Datasets'[Date]))))
I get nothing... any suggestions from the gurus?? I'd really appreciate the help!
Solved! Go to Solution.
Hi @TagMan
If you have a Year column in the Date table, you can try this measure
Cumulative Count PY =
VAR _year = MAX ( 'Date'[Year] ) - 1
VAR _maxDate = EDATE ( MAX ( 'Date'[Date] ), -12 )
RETURN
CALCULATE (
COUNT ( Opportunity[RecordID] ),
ALL ( 'Date'[Date] ),
'Date'[Year] = _year,
'Date'[Date] <= _maxDate
)
EDATE function (DAX) - DAX | Microsoft Docs
----------------------------------------------------------------------
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
Hi @TagMan
If you have a Year column in the Date table, you can try this measure
Cumulative Count PY =
VAR _year = MAX ( 'Date'[Year] ) - 1
VAR _maxDate = EDATE ( MAX ( 'Date'[Date] ), -12 )
RETURN
CALCULATE (
COUNT ( Opportunity[RecordID] ),
ALL ( 'Date'[Date] ),
'Date'[Year] = _year,
'Date'[Date] <= _maxDate
)
EDATE function (DAX) - DAX | Microsoft Docs
----------------------------------------------------------------------
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
Hi @TagMan , try this:
RecordID PY Accumulated =
CALCULATE (
[RecordID PY],
(
FILTER (
ALL ( 'Calendar Master From Sharepoint Raw Datasets' ),
'Calendar Master From Sharepoint Raw Datasets'[YourYearSlicer]
= MAX ( 'Calendar Master From Sharepoint Raw Datasets'[YourYearSlicer] )
&& 'Calendar Master From Sharepoint Raw Datasets'[Date]
<= MAX ( 'Calendar Master From Sharepoint Raw Datasets'[Date] )
)
)
)
Your way smarter than me...I don't know what value [youryearslicer] is.. I've tried [Date]... but end up with no data still...I appreciate your help!
[YourYearSlicer] means your Year filter (if you have one I assume). Check your dataset otherwise kindly provide your pbix file here removing the critical info 🙂
Cheers xx
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |