Last Year Week Measure

[MH3]

Hellot,

I need your help in fixing a Measure for a BI Report

We have a client who uses Seasonal Calendar and which is different from the normal calendar

Which Starts from May and End on April 
and the Calendar is Divided into two Seasons
Winter and Summer

Summer Season Starts from = May, June, July, August, September,October
Winter Season Starts from = November, January, Feburary, March, April

We have a Report which shows Revenue for the Current Year and the Current Season
I.e Year would be FY 21-22
and Season Summer FY 21-22

Which have Months from  May to October

Now, In the report we have a Previous Year Visual which shows the Revenue  for Last Year Same Season and Week which is selected



As you see the SW-01 is Selected which is Summer Week for the Year 21-22 
and on Year 20-21 which is Previous Year we dont have data for the Week of May.

But it Shows 230 which is the Last Week  Revenue of April 

This is the different report which shows Weekly data and the dates in that week,
So, May 2020 has no Data 



And The April 2020's Last week which was Week 27
The Revenue was 230 


So, if you see this again



It Shows Arosa Revenue is 230 for the Year 20-21 which is the previous of ( 21-22 the Current Year )
I think the Measure of Weekly Revenue LY is creating a problem in here

As you see in this Visual 


The Weekly Revenue LY  for Arosa 21-22 
Which is 20-21  is Showing 230 Which is wrong because I have used ALL in the measure which has ignored the filter and showing the values 

Week Revenue LY = 
CALCULATE (
    SUM ( Bi_Turnover[Revenue] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[FiscalYearNumber]
            = SELECTEDVALUE ( 'Date'[FiscalYearNumber] ) - 1
            && 'Date'[Fiscal Week] = SELECTEDVALUE ( 'Date'[Fiscal Week] )
    )
)

 
The FiscalYearNumber is a Whole Number Column in my Date Table



The All Ignored the filters so that's the 21-22 May's Previous Data for 20-21 May isnt showing the correct result
is there any way to include the Filter again which the ALL has removed.
I am kind stuck in this

[MH3]

@amitchandak 
@Ashish_Mathur 

Any Help Please!

[v-kkf-msft · Accepted solution]

Hi @MH3 ,

I think this is caused by the same number of Fiscal weeks from 2020-04-27 to 2020-05-03, try to add the month filter.

Week Revenue LY = 
CALCULATE (
    SUM ( Bi_Turnover[Revenue] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[FiscalYearNumber]
            = max( 'Date'[FiscalYearNumber] ) - 1
            && 'Date'[Fiscal Week] = max( 'Date'[Fiscal Week] )
            && 'Date'[FiscalMonth] = max( 'Date'[FiscalMonth] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MH3 , if you have week and year in the context try like

Week Revenue LY = 
CALCULATE (
    SUM ( Bi_Turnover[Revenue] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[FiscalYearNumber]
            = max( 'Date'[FiscalYearNumber] ) - 1
            && 'Date'[Fiscal Week] = max( 'Date'[Fiscal Week] )
    )
)

[MH3]

Hi @amitchandak 

I have Season Slicer and Hotel Slicer in my Context


The Weekly Revenue LY Shows the Same Result for May 2021 which is 65,590, but the May 2020 has no Data, so it should not show this revenue, this revenue was in April 2020' Week #27

2021 May 




2020 MAY

The Weekly Revenue Should show Blank of May 2020 if the Week is Selected from 1st May 2021

The Issue is only in the 1st Week of May 2021 to its Previous May 2020