measure value incorrect when no data present

[Anonymous]

Hi, I'm developing a report and i have data starting from 2017.Q2 only to current Quarter 2019Q2.

I have a table visual with "Total Sales", and "ThreeyearAverage Total Sales" with below measures for all reportng period from the Agg table.

Total Sales = SUM(SalesAgg[Sales])

3 Year Sales= CALCULATE(SUM(SalesAgg[Sales]) +0    -- I also tried to use above Measure [Total Sales] here but still no luck.

        ,   FILTER(all('DATES'),

       AND ( 'DATES'[Year] >= SELECTEDVALUE('DATES'[Year] ) -2,

                  'DATES'[Quarter]= SELECTEDVALUE('DATES'[Quarter] )

               ) )

Three-year average is showing correctly for when data is present for all the 3 years for a selected Quarter. But for example for 2017.Q1, because there is no data available for 2016Q1, 2015Q1; TotalSales must be = [3 year Sales]. But as you see in the picutre values are not same.

here in the picture, we have all the Reporting Periods just to demonstrate the issue by completely removing Reporting Period Slicer. when I have Slicer, it will show that particular Quarter and data is same as shown in the picture.

[sturlaws · Accepted solution]

Hi @Anonymous,

I have had a look at your code, and you need to rewrite it a little to get what you are looking for. The code you posted:

3 Year Sales =
CALCULATE (
    SUM ( SalesAgg[Sales] ) + 0,
    FILTER (
        ALL ( 'DATES' ),
        AND (
            'DATES'[Year]
                >= SELECTEDVALUE ( 'DATES'[Year] ) - 2,
            'DATES'[Quarter] = SELECTEDVALUE ( 'DATES'[Quarter] )
        )
    )
)

calculates, according to the conditions of the FILTER-function that you should sum all values where year is greater than or equal to the year in the current filter context - 2. When you look at the first row of your table, your earliest period is Q2 2017. This means that your code is summing all rows where year greater than or eqaul to 2015.

You also have a condition which says you should only sum rows where the quarter number is the same as the current quarter number. Not sure whether this is intentional or not. So again, looking at your first reporting period Q2 2017, the [3 year sale] is the sum of all Q2 where year is greater than 2015.

I am not entirely sure what you are trying to do, so I can only make an educated guess as to what your measure should look like:

3 Year Sales =
CALCULATE (
    SUM ( SalesAgg[Sales] ) + 0,
    FILTER (
        ALL ( 'DATES' ),
        'DATES'[Year]
            >= SELECTEDVALUE ( 'DATES'[Year] ) - 2
            && 'DATES'[Year] <= SELECTEDVALUE ( 'DATES'[Year] )
            && 'DATES'[Quarter] = SELECTEDVALUE ( 'DATES'[Quarter] )
    )
)

Cheers,
Sturla

[Anonymous]

@sturlaws  Thank you very much. 

I dont know how i missed that part. I felt like a stupid for a minute. 

Yes, I fixed the Measure and now it's working correctly. we do not want all future years. Only 3 years worth.

[sturlaws]

Hi @Anonymous ,

I had a look at the the code you provided, and I am not sure it calculates what you want it to calculate

3 Year Sales =
CALCULATE (
    SUM ( SalesAgg[Sales] ) + 0,
    FILTER (
        ALL ( 'DATES' ),
        AND (
            'DATES'[Year]
                >= SELECTEDVALUE ( 'DATES'[Year] ) - 2,
            'DATES'[Quarter] = SELECTEDVALUE ( 'DATES'[Quarter] )
        )
    )
)

The first part of you conditions inside the FILTER-function:

'DATES'[Year] >= SELECTEDVALUE ( 'DATES'[Year] ) - 2

says include all years which is greater than the year in the current filter context - 2. So when you look at reporting period Q2 2017, you are filtering the SalesAgg-table by looking at all years greater than or equal to 2015. Which means you are looking at all years present in SalesAgg.

Next, the second condition of your FILTER-function

'DATES'[Quarter] = SELECTEDVALUE ( 'DATES'[Quarter] )

says only look at quaters where the quarter number is the same as the quarter number in the current filter context. So again, looking at Q2 2017, your measure is returning the sum of all rows where quater number = 2 and year>=2015. From your post it is not clear if you want to sum quater by quarter, so this may be correct.

You have not included a sample report of your model, so I can only make an educated guess about a code that will give you what I think you are trying to achive. Assuming that you want to calculate for the same quater for the last 3 years, the code should look something like this

3 Year Sales =
CALCULATE (
    SUM ( SalesAgg[Sales] ) + 0,
    FILTER (
        ALL ( 'DATES' ),
        'DATES'[Year]
            >= SELECTEDVALUE ( 'DATES'[Year] ) - 2
            && 'DATES'[Year]
                <= SELECTEDVALUE ( 'DATES'[Year] )
            && 'DATES'[Quarter] = SELECTEDVALUE ( 'DATES'[Quarter] )
    )
)

Cheers,
Sturla