Cumulative Total for Prior Year (PY) w/ PY Calendar Adjusted by Day of Week

[1Kash_PBI]

Hello,

For the current year (CY), I got my cumulative total measure to work for selected time periods with showing blanks for futrue periods (shown below).

Next I want to produce something similar for the prior year (PY), but with the PY calendar adjusted to align by day of the week (eg Wed to Wed)

Basically I want to take the CY date and subtract 364 days (This is not a perfect calculation when you incorporate leap year and a 53rd week, but works for this project)... Can't quite get it to work.

Hoping the community can help me out,
Thanks!

Cumulative New Pawns $ = 
VAR LastPawnDate = CALCULATE(   LASTDATE(FactPawn_Rollforward[close_date] ), ALL(FactPawn_Rollforward) )

RETURN
IF(SELECTEDVALUE( DimDate[Date] ) > LastPawnDate, BLANK(),
    CALCULATE( [New Pawns $] ,
        FILTER( ALLSELECTED( DimDate),
            DimDate[Date] <= MAX( DimDate[Date] ) )))​

[Greg_Deckler]

@1Kash_PBI - I'm not following your code very well. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[1Kash_PBI]

Thanks for the reply!

Unfortunately, the results are not a cumulative or running total by day ,like my current year code does.

[1Kash_PBI]

Thanks for the reply and apologies for not reading the wiki beforehand... Let me try again.

The code I posted was for Current Year

• A Cumulative Measure that sums up sales for the current year
• Uses the last date a sale was made, as the Max Date i`n the chart or matrix
• Uses whichever date I select in the chart or matrix as the starting point of the cumulative 
• Works as expected for the current year

What I need is for Prior Year

• Something that performs similarly to sameperiodlastyear
  • but uses the dynamic date range provided from the above For Current Year code
• Also aligns the prior year calendar, to the current year calendar, by the day of week (example shown below in table)

How to get from Point A to Point B: Noob explanation

• First, needs to use date range provided from the For Current Year code
• Then, needs to align the prior year dates to current year dates, by the day of week (example shown below in tables)
  • (This is the best I got; maybe there's a better way the experts here can teach me)
    • My Math Logic: was to Subtract 364 days from each individual date produced from the current year code results

I hope this makes more sense... Thanks again for looking over.

Current Year Dates

1/5/2020 Sun
1/6/2020 Mon
1/7/2020 Tue
1/8/2020 Wed
1/9/2020 Thu
1/10/2020 Fri

Prior Year Dates aligned to Current Year Dates, by day of week (I subtracted 364 days from eacg current year date)

1/6/2019 Sun
1/7/2019 Mon
1/8/2019 Tue
1/9/2019 Wed
1/10/2019 Thu
1/11/2019 Fri

[Ashish_Mathur]

Hi,

Try this measure

=calculate([your measure],datesbetween(calendar[date],min(calendar[date])-364,max(calendar[date])-364))

Ensure that to your visual/slicers, you drag year and month from the calendar table.

Hope this helps.

[1Kash_PBI]

Also, my visual is showing by day, if that helps...

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[1Kash_PBI]

Thanks again!

In the below table I've provided prior year sales by day as well as the expected cumulative results...

Reminder Prior year is defined as current years date less 364 days)

Also, I provided a link to some sample data that includes the visual with current year cumulative results performing as expected.

SampleData.pbix 

Date	Cumulative New Sales $ PY	By Day New Sales $ PY
3/1/2020	-	-
3/2/2020	3,715	3,715
3/3/2020	4,840	1,125
3/4/2020	5,615	775
3/5/2020	7,310	1,695
3/6/2020	7,390	80
3/7/2020	7,750	360
3/8/2020	7,750	-
3/9/2020	8,670	920

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = CALCULATE([Cumulative New Sales $ PY],DATESBETWEEN(DimDate[Date],MINX(ALLSELECTED(DimDate[Date]),DimDate[Date]),MAX(DimDate[Date])))

Hope this helps.

[1Kash_PBI]

Thank you sir! I'm beyond thankful!!

As a new member, I truly appreciate you taking time to help me.

You rock!

[Ashish_Mathur]

You are welcome.