Calculate cumulative sum from beginning of year to filter selection

1
votes

I have two tables

  1. Calendar - Contains columns for year, quarter, YearQuarter

  2. Sales - contains columns for Entity sold, sale price, date of sale, quarter of sale, year of sale.

I have a filter in my dashboard for Year and Quarter. I would like to create a measure that calculates the cumulative sum of sales from the beginning of the selected year to the selected quarter.

e.g. if Year = 2020, and Quarter = Q3, the measure should calculate the sum of sales from the beginning of 2020 until the end of Q3 2020.

enter image description here enter image description here enter image description here

1
powerbidaxpowerbi-desktop

1 Answers

0
votes

If you had a well-formed date table, you could use time-intelligence functions DATESYTD or TOTALYTD as explained in The hidden secrets of TOTALYTD.

However, your calendar table doesn't qualify, so you need to do things a bit more manually.

Sales YTD =
VAR SelQtr = SELECTEDVALUE ( Calendar[Quarter] )
RETURN
    CALCULATE (
        [Sales],
        ALLEXCEPT ( Calendar, Calendar[Year] ),
        Calendar[Quarter] <= SelQtr
    )

This removes any calendar filtering except for the year and filters for quarters up to the selected quarter.