Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Totals Issue with Measure

Created a new Measure which calculates the Prior year Sales Amount WTD, On the Power BI report there are 3 scliers to select Year, Week and Status. Here is the Dax Measure;

SalesAmtPY:=
VAR DateRange =
    FILTER (
        ALL ( 'DimDate' ),
        'DimDate'[AdjustedYearNumber]
            = VALUES ( 'DimDate'[AdjustedYearNumber] ) - 1
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearNumber] ),
                'DimDate'[AdjustedWeekofYearNumber], 'DimDate'[AdjustedWeekofYearNumber]
            )
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearandDayofWeekNumber] ),
                'DimDate'[AdjustedWeekofYearandDayofWeekNumber], 'DimDate'[AdjustedWeekofYearandDayofWeekNumber]
            )
    )

     
RETURN
    IF (
        ISBLANK ( [SalesAmt] ),
        BLANK (),
        CALCULATE ( 'FactSales'[SalesAmt], DateRange, ALL ( DimStatus[StatusDescription],DimStatus[Status] ) )
    )

On the Matix report I need show Channel ,Distrinct ,Store Number ,Status,SalesAmt , SalesAmtPy and status can change over time So when calcualting PY I need to ignore the Status from Prior Year and show only the current year Status with SalesAmt and SalesAmtPy on one line.To acheive this I used the above Calc which seems to be working fine but the totals and subtotals in matrix report or totals in tabular report are not correct. Only at the lowest level in matrix they are calculating fine. on the higher level it is not .