Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 .
Solved! Go to Solution.
@Anonymous
You may take a look at the post below.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#M56
@Anonymous
You may take a look at the post below.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#M56
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |