Running Totals and Charts

[Planted_Baker]

Dear All,

A newbie to Power Bi, newbie to this forum as well as newbie to effectively ask queries here. So please pardon if something is going amiss here. I have tried to ask a few questions here before but somehow i the data provided by me was either not enough to answer the query or my queries weren't clear enough. Here's another attempt; this time with a sample .pbix file as well as data file in below links 🙂 Look forward to all the advise:

Please find attached herewith a sample .pbix file (same file attached everywhere):

Google Drive: https://drive.google.com/drive/folders/148r27gZ-6x8uN_0gNXcXmdoYImEU4idI

Dropbox: https://www.dropbox.com/s/s58yr4xfy4rz9gj/Files.zip?dl=0

Why do the below formula's not work ? Can you please help with the corrected ones:

1. Trying to calculate rows where 'W_Date' is not blank. Kindly note that blank and non applicable 'W_Date' rows in attached file are filtered out. 

W'd = CALCULATE(COUNTROWS(Data), Data[W_Date]<>"")

2. Trying to insert new cloumn if activity 'W' is complete or not.

W_Chk = IF(Data[W_Date]<>"","Y","N")

3. Trying to calculate weekly running totals for 'W' using Rolling_W. Its kinda working as 'W_Chk1' = 1 for all rows. I want it to work with COUNTROWS with specific filter context to 'W_Date' column. Running total shall ignore all rows with 'W_Date' = blank.

Rolling_W = CALCULATE(
    SUM(Data[W_Chk1]),
    Data[W_WN] <= MAX(Data[W_WN]))

4. in 3 above, I intend to calculate running totals for 'P' and 'R' in a similar way. For 'P', ignore everything except "Y" & for 'R' ignore everything except "100".

5. In Visual Weekly 'W' & Weekly 'W1', only difference is mapping the WEKNUM. I intend to count all data with respect to 'Dates[WN]' column. Meaning, for '2021 W-26' from 'Dates[WN]',

a) get weekly total & running total of 'W' when 'W_Date' falls in '2021 W-26' range

b) get weekly total & running total of 'P' when 'P_Date' falls in '2021 W-26' range

c) get weekly total & running total of 'R' when 'R_Date' falls in '2021 W-26' range

6. I am interested to get data of only last 12 weeks in the visual. How to hide rest of the data, but can be seen if required.

7. How to superimpose weekly totals of 'P', 'W' & 'R' in Line Chart 

8. In a different visual, i intend to make Cards visual of these different matrices using 'Dates[WN]' as slicer. It shall show weekly totals of 'W', 'R' & 'P' on the cards. 

9. Can you please suggest the best visuals/ways/ideas to present this data ?

10. Can you please suggest/optimize the functions/measures/columns I've added to original data in order to automate the process as much as possible.

[Planted_Baker]

@hnguy71 

I am sorry to add a new question to this thread even after the previous accpeted solution. But for some reason I am get different values for "W'd" when i use "Dates[WN]" and "Data[W_Date]" as a slicer.  The values returned when using "Data[W_Date]" are accurate. Similarly, values returned for "P'd" when i used "Data[P_Date]" and for "R'd" when "Data[R_Date]" are accurate. I however want all the values to be returned using "Dates[WN]" as slicer.

Is it because there is no date context provided in the formula of "W'd" ? I am kind of confused how Power Bi is doing the totals. Will be obliged if you can help.

[hnguy71]

hi @Planted_Baker are you familiar with the concept of a disconnected slicer table? If not, to explain, it is a table not connected to your model and typically holds one or two columns to control what is displayed. In this case, you want all your measures to work with Dates[WN] so you'll have a new table likely with this:

SlicerWN = ALL(Dates[WN], Dates[Week_Value])

And then your measure(s) would reference the selectedvalue. As an example, to modify your RT_WeekCheck it would be similar to this:

RT_WeekCheck = 

VAR _CurrentDate = SELECTEDVALUE(SlicerWN[WN], MAX(SlicerWN[WN]))
VAR _CurrentWeek = SELECTEDVALUE(SlicerWN[Week_Value], MAX(SlicerWN[Week_Value]))

RETURN

CALCULATE( SUM(Data[W_Chk1]), ALL(Dates), Dates[Date] <= _CurrentDate && Dates[Week_Value] = _CurrentWeek )

This should definitely give you the right results. If  you need further assistance, please attach an updated pbix and showcase your expected output and I can help.

[hnguy71 · Accepted solution]

Hi @Planted_Baker ,

1. Your expression is not comparing like with like. Try this instead:

W'd = CALCULATE( COUNTROWS(Data), Data[W_Date] <> BLANK() )

2. Same thing. Try this instead:

W_Chk = IF( Data[W_Date] <> BLANK(), "Y", "N" )

3.  You need to check what is the current date and week and reset the rolling total against that. Try this instead:

RT_WeekCheck = 

VAR _CurrentDate = MAX(Dates[Date])
VAR _CurrentWeek = MAX(Dates[Week_Value])

RETURN

CALCULATE( SUM(Data[W_Chk1]), ALL(Dates), Dates[Date] <= _CurrentDate && Dates[Week_Value] = _CurrentWeek )

4. For all P, R, etc. you would need to just add the condition within your expression to pick up each requirements. Here's an example for the rolling total for "P". Note that I'm referencing the base rolling total measure:

RT_P = CALCULATE( [RT_WeekCheck],  Data[P] = "Y" )

5. Same set-up. However, I would instead change your week_value column within the Dates table to something like this instead:

WeekYearInt = VALUE(YEAR([Date]) & WEEKNUM([Date], 2))

6. Once all your base measures have been created, you can use something similar of this nature to get only the last 12 weeks of data:

Last_12_Weeks_RT_WeekCheck = 

// what is my current week within my dataset?
VAR _LastDate = CALCULATE(MAX(Data[W_Date]), ALL(Data))

// what is my current week number within the year?
VAR _WeekOfYear = CALCULATE(MAX(Dates[WeekYearInt]), Dates[Date] = _LastDate)

// how many weeks to return?
VAR _WeeksToShow = 12

// what is my first date to show?
VAR _FirstDate = CALCULATE( MIN(Dates[Date]), FILTER(ALL(Dates), Dates[WeekYearInt] = _WeekOfYear - _WeeksToShow) )

RETURN

CALCULATE( [RT_WeekCheck], KEEPFILTERS(Dates[Date] >= _FirstDate && Dates[Date] <= _LastDate))

7. This should be coming from you "WN" column within your Dates table.

8. Not a question.

9. This would be a question for your Product Manager and/or Product Owner. You would ideally come up with personas and understand who is your intended audience and why is this dashboard relevant and answers their question(s).

[Planted_Baker]

@hnguy71 Thanks a lot for that detailed answer ! Everything worked as i wanted ! You proved to be godsent !!!