Show Current year Sales and Prior Year Sales in One line

[Anonymous]

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

[kamalmsharma]

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 

[v-juanli-msft]

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

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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...

[Anonymous]

I tried your Measure, didn't work for me. Thanks!!