Line Chart to show Cumulative Current Year WTD vs Cumulative Prior to Last Year WTD

[Anonymous]

Hi,

I am trying to create a Line Chart to show Cumulative `Current Year Week to Date` vs Cumulative `Prior to Last Year Week to Date`.

I have a Salesforce `Opportunity` table and a `dimDate` table joined by an Inactive relationship on `Date` from the `dimDate` table to `CreatedDate` on the `Opportunity` table. I don't want to make it active as there are other date columns in `Opportunity` table connected to `dimDate` on `Date` column and one of them is active.

Current Year:

Opp($) = CALCULATE(SUM('Opportunity'[USD_Amount__c]), 'Opportunity'[USD_Amount__c] <= 1000000, USERELATIONSHIP(dimDate[Date],'Opportunity'[CreatedDate]))

Opp($) - RunningTotal = CALCULATE([Opp($)], FILTER(ALLSELECTED('dimDate'[Date]), dimDate[Date] <= MAX(dimDate[Date])))

Prior to Last Year:

Opp PPY($) = CALCULATE(SUM('Opportunity'[USD_Amount__c]), 'Opportunity'[USD_Amount__c] <= 1000000, USERELATIONSHIP(dimDate[Date],'Opportunity'[CreatedDate]), DATEADD(dimDate[Date], -2,YEAR))

Opp($) PPY - RunningTotal = CALCULATE([Opp PPY($)], FILTER(ALLSELECTED('dimDate'[Date]), dimDate[Date] <= MAX(dimDate[Date])))

I have a `WeekName` column in `dimDate` table that i add to X-Axis which gives the week names as `Week 1, Week 2, Week 3...`. I am not sure above measures are the right way to calculate cumulatives for Prior to Last Year and Current Year as the expected output is:

but getting:

It seems the running totals for both measures is not calculating from Week1, but adding values to total from previous weeks, so the amounts not showing correctly.

Date        |  WeekName|  Opp($)        |  Opp($) - RunningTotal  |  Opp PPY($)  |  Opp($) PPY - RunningTotal  |
2019-01-01  |  Week 1  |  $2,000.0      |  500,000,000.00         | 10,000.00    |  400,000.00|
2019-01-02  |  Week 1  |  $20,000.0     |  500,020,000.00         | 50,200.00    |  460,200.00|
2019-01-03  |  Week 1  |  $2,000,000.0  |  502,020,000.00         |  5,000.00    |  465,200.00|
2019-01-04  |  Week 1  |  $400,000.0    |  502,420,000.00         |  10,000.00    | 475,200.00|
2019-01-05  |  Week 1  |  $20,000.0     |  502,440,000.00         |  10,000.00    | 485,200.00|
2019-01-06  |  Week 1  |  $30,000.0     |  502,470,000.00         |  40,000.00    | 525,200.00|
2019-01-07  |  Week 2  |  $5,000.0      |  502,475,000.00         |  10,000.00    | 535,200.00|
2019-01-08  |  Week 2  |  $1,000.0      |  502,476,000.00         |  1,000.00     | 536,200.00|

What i want is running totals to start from Week 1 i.e. 2019-01-01 onwards i.e. for e.g. for `Week 1 - Opp($) - RunningTotal` should show:

Date        |  WeekName|  Opp($)        |  Opp($) - RunningTotal  |  Opp PPY($) |  Opp($) PPY - RunningTotal  |
2019-01-01  |  Week 1  |  $2,000.0      |  2,000.00               | 10,000.00   |  10,000.00|
2019-01-02  |  Week 1  |  $20,000.0     |  22,000.00              | 50,200.00   |  60,200.00|
2019-01-03  |  Week 1  |  $2,000,000.0  |  2,022,000.00           |  5,000.00   |  65,200.00|
2019-01-04  |  Week 1  |  $400,000.0    |  2,422,000.00           |  10,000.00  |  75,200.00|
2019-01-05  |  Week 1  |  $20,000.0     |  2,442,000.00           |  10,000.00  |  85,200.00|
2019-01-06  |  Week 1  |  $30,000.0     |  2,472,000.00           |  40,000.00  | 125,200.00|

Any help would be most appreciated.

[Anonymous]

Hi @amitchandak, just a query related to my OP. Hope you can provide a suitable solution.

If you see my OP, i have shared a Google Data Studio chart showing the weeks as Week1, Week 5, Week 9, Week 13 etc...till Week 33 (say current week). I think this is done automatically by DataStudio to fit the data without showing horizontal scrollbars. How do i achieve the same in the Power BI chart?

[Anonymous]

@amitchandak , thanks. I understood your calculations now. Though i had to create a `IsYTD` logical column in the `dimDate` table show the weeks correctly from Week 1 to Week till date i.e. Week 35....and i also missed adding the "<=" (instead of "=") to the measures to get the running totals.

JFYI,

  CurrentDate = Date.From(DateTime.LocalNow(), "en-US"),
  CurrentDateRecord = Table.SelectRows(PreviousStep, each [Date] = CurrentDate),
  CurrentYear = CurrentDateRecord{0}[Year],
  InsertYTD = Table.AddColumn(PreviousStep, "IsYTD", each if CurrentYear = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),

Cheers!

[Anonymous]

@amitchandak i am not getting the desired output. Also, cumulative current weeks this year till date is not showing at all and weeks are starting from Week 2 onwards. cumulatives are not being calculated. See screenshot of both the Line Chart and the same shown as a Table :

same weeks current year = CALCULATE([Opp($)], FILTER(ALL('dimDate'),'dimDate'[Year]=MAX('dimDate'[Year]) && 'dimDate'[WeekNo] = MAX('dimDate'[WeekNo])))

same weeks prior to last year = CALCULATE([Opp($)], FILTER(ALL('dimDate'),'dimDate'[Year]=MAX('dimDate'[Year])-2 && 'dimDate'[WeekNo] = MAX('dimDate'[WeekNo])))

[amitchandak · Accepted solution]

@Anonymous , with help from a date table having year and week

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))

LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

same week last year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

refer if needed

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8