Cumulative "Year-Week" totals presented on a Line and clustered column chart

[Mattpar]

Hi,

I am quite new to DAX and having trouble creating two measures that calculate the "cumulative Actual to date" and "cumulative Budget to date" by "Year-Week" .

Trying to sort out the cumulative actual first, I created a measure called "Actual Spent" which is =

SUMX('sampletable','sample table'[Actual(hours)])

Then I found below within other forum posts and tried to modify, but could not make it work as I could not find how to link it to Year-Week column and how to start the totals from 2019-14.

CALCULATE( [Actual Spent], FILTER ( ALL ('sample table' ), ('sample table'[Item Date] <= MAX ( 'sample table'[Item Date] ))

My end goal is to be able to present this on a line and clustered column chart where;

1 -  X axis will be "Year-Week",

2- Column values will be Actual (hours) & Budgeted (hours),

3 - Line graph values will be the cumulative Actual (Hours) & Budgeted (Hours) total, starting from 2019-14 to the latest filtered/presented Year-Week value for a particular discipline.

Below is the sample dataset visual and the graph that I am trying to plot (without the line charts).

While I have granular level data for actuals, my budget values are at weekly summary level, therefore I am working with Year-Week column.

[Icey]

Hi @Mattpar ,

Is this problem solved?

If it is, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please share me more data without sensitive information.

Best Regards
Icey

[Mattpar · Accepted solution]

Thanks for the responses @Icey and @amitchandak 

Without creating a separate dates table, I managed to get the running total's, by using;

CALCULATE( [Actual Spent], FILTER ( ALL ('sample table' ), ('sample table'[Item Date] <= MAX ( 'sample table'[Item Date] ))

However, when I tried to present this by filtering partner and department in a matrix or graph, I realized that running totals does the total for combined version (i.e total of quantity for dep without any consideration of partner). Then I created two measures that are modified version of above. 

Also to calculate the Actuals from 2019-14, I added an additional criteria  'sample table'[Year-Week] >= "2019-14" into my Actuals measure. 

For now everything seems to be working..I know everyones case might be different but I will leave the measure formulas here thinking that it might help someone.

Budgeted Running Total = CALCULATE([Budgeted Hours Total], FILTER(ALLEXCEPT ('sample table', 'sample table'[Dep],'sample table '[PARTNER]), 'sample table'[Year-Week] <= MAX('sample table'[Year-Week])))

Actual Running Total = CALCULATE([Actual Spent], FILTER(ALLEXCEPT('sample table','sample table'[PARTNER], 'sample table'[Dep]), 'sample table'[Year-Week] >= "2019-14" && 'sample table'[Year-Week] <= MAX('sample table'[Year-Week])))

[amitchandak]

Create a date/ calendar table. move the date filter there. In that table define your week and use that as filter. Also calculate week start and end date if needed.

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Week Start date = DATEADD('Compare Date'[Compare Date],-1*WEEKDAY('Compare Date'[Compare Date])+1,DAY)
Week End date = DATEADD('Compare Date'[Compare Date],7-1*WEEKDAY('Compare Date'[Compare Date]),DAY)

CALCULATE( [Actual Spent], FILTER ( ALL ('sample table' ), ('sample table'[Item Date] <= MAX ( 'date'[Date] ))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601