how do you calculate an accumulated total of the prior years count of record IDs

[TagMan]

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: 

RecordID PY =

Calculate(COUNT(Opportunity[RecordID]), SAMEPERIODLASTYEAR('Calendar Master From Sharepoint Raw Datasets'[Date].[Date]))

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!

[liuqi_pbi · Accepted 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!

[ronaldbalza2023]

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] )
        )
    )
)

[TagMan]

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!

[ronaldbalza2023]

[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