Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

sifar786_0-1629876639006.png

but getting:

sifar786_1-1629876908955.png

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Not applicable

@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
Not applicable

@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 :

 

sifar786_0-1629881691108.png

 

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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.