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.
Hi,
I'm trying to create a DAX Measure for Prior year and need some help. My Model has one Fact Table and 4 dimensions. FactSales, DimStore, DimDate, DimCompStatus, DimStyle. DimCompStatus has data this way ; for each week and for each Store their Status is Stored. Here is an example of how Prior Year values shows up because of CompStatus of a Store.
For Store 402 and 412 there are 2 lines because the compStatus of last year is different. I would like to see the current year comp status and SalesAmt and SalesAmtpy in one line like this;
402 C 34325 40349
412 O 66347 68893
Here is my PY Calc
SalesAmtPY:= VAR DateRange = FILTER ( ALL ( 'DimDateRetail' ), 'DimDateRetail'[AdjustedYearNumber] = VALUES ( 'DimDateRetail'[AdjustedYearNumber] ) - 1 && CONTAINS ( VALUES ( 'DimDateRetail'[AdjustedWeekofYearNumber] ), 'DimDateRetail'[AdjustedWeekofYearNumber], 'DimDateRetail'[AdjustedWeekofYearNumber] ) && CONTAINS ( VALUES ( 'DimDateRetail'[AdjustedWeekofYearandDayofWeekNumber] ), 'DimDateRetail'[AdjustedWeekofYearandDayofWeekNumber], 'DimDateRetail'[AdjustedWeekofYearandDayofWeekNumber] ) ) RETURN CALCULATE ( [SalesAmt], DateRange)
Thanks
Hi Sri,
Try using ALL(tablename, tablename[compstatuscode]) as a filtering condition to your salesamount measure for cuurent year as well as last year. This will ignore compstatus code column as a filtering condition in the table.
Cheers
Kamal
www.addendanal
Hi @Anonymous
Hi @Anonymous
Do you have date column(of date format) or only "year","day" (of number format) columns in the dataset?
It is important for me to know so to decide if we can use time intelligence function as this thread
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
I tried your Measure, didn't work for me. Thanks!!
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 |
---|---|
110 | |
98 | |
79 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |