AVERAGE OF SELECTED PERIOD FOR PREVIOUS YEAR

[Anonymous]

Dear All,

I have a table with the corresponding Calendar Weeks per year, and I need to take the average of selected calendar weeks for the previous year. Could you help me?

For example;

CW 52 - year 2020: 100 ------> Looks for value in CW 52 - year 2019: 70

CW 01 - year 2021: 50   ------> Looks for value in CW 01 - year 2020: 80

So the card has to show the average of both values:

CW 52 - year 2019: 70

CW 01 - year 2020: 80

Average of selected period for previous year: 75

That's my table, I need the third card, please.

I tried - and another ways and nothink worked out:

Average PY = IF([Average CY]=0,BLANK(),CALCULATE([Average CY],SAMEPERIODLASTYEAR('dCalendar'[Date])))

Thanks!

[amitchandak]

@Anonymous , You should simply try this

CALCULATE([Average CY],SAMEPERIODLASTYEAR('dCalendar'[Date])

or

CALCULATE([Average CY],dateadd('Date'[Date],-1,Year))

or create a week rank on week year or week start date - new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

 and try measure like

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Refer Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi, @amitchandak

Thanks for your support, but it's still not working...

CALCULATE([Average CY],SAMEPERIODLASTYEAR('dCalendar'[Date])

It's the bringing the average corresponding to the current year, not the previous one.

CALCULATE([Average CY],dateadd('Date'[Date],-1,Year))

In my case it's no applicable because I need a dynamic measure, so i can't define a year.

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

I already have a week rank, but unfortunately I reproduced the steps below, and for 'Date'[Week Rank] it's acceptable only measures, not columns, so it's not possible to apply this formula there.

[amitchandak]

@Anonymous , I doubt that your calendar might noy have all dates. SAMEPERIODLASTYEAR do give same year data. Can check do you have all required date in calendar

[Anonymous]

@amitchandak, I have a calendar table, and all the years that I need. In my case I have data for 2020 and 2021, so my calendar is from 2019 to 2021. As you can see below the previous year is being considered.

[Anonymous]

Hi @Anonymous,
In my opinion, I'd like to suggest you extract the selected date range from your filter. (extract the weeknum and year) Then you can use this to calculate the previous range and use them as filter conditions on the expressions with the fact table to get correct results.

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, @Anonymous .

I also tried it. As you can see I created a calendar and extracted the corresponding year and calendar week. With these values I built my measure... and it's not working. Is there any way to get my support from you? Someone that can look into my dashboard.

Thanks!

[Anonymous]

HI @Anonymous,

Can you please share a pbix file with some dummy data? It is hard to rebuild the sample as raw data structure from your screenshots.

Notice:

1. remove sensitive data before your share.

2. You can update to onedrive for business to share your file.

Regards,

Xiaoxin Sheng

[Syndicate_Admin]

Hi @Syndicate_Admin and @v-shex-msft.

Is there any email that I can share it? I tried by OneDrive, and it's not working.

I also can't send private messages with the file to you.

I already took out all sensitive data.

Thanks!

[Anonymous]

HI @Anonymous,

Unfortunately, we can't use personal email to receive sample data.
Please take a look at the following link about sharing files from onedrive or you can try to copy your sample data and paste it to your reply in table format. (export your data to excel and copy excel sheet ranges to your reply contents, then these content can be recognized as table format)

Share OneDrive files and folders 

Regards,

Xiaoxin SHeng

[Anonymous]

Hi, @Anonymous 

Please consider the link to my OneDrive and respective file:

https://1drv.ms/x/s!Aj0SVa4nvP8vnWh8J4xD0Hlz2oVA

Thank you!

[Anonymous]

HI @Anonymous,

Did the 'Average CY' formula calculate based on the time intelligence function? If this is the case, you can't simply nester with other date functions.
If not, you can try to use the following measure expressions if it works on your side:

Measure =
VAR currDate =
    MAX ( 'Order Value_Contact'[Date] )
VAR currCW =
    MAX ( 'Order Value_Contact'[CW/Year] )
VAR prevDate =
    CALCULATE (
        MAX ( 'Order Value_Contact'[Date] ),
        FILTER (
            ALLSELECTED ( 'Order Value_Contact' ),
            [CW/Year] <> currCW
                && [Date] < currDate
        )
    )
VAR prevCW =
    CALCULATE (
        MAX ( 'Order Value_Contact'[CW/Year] ),
        FILTER ( ALLSELECTED ( 'Order Value_Contact' ), [Date] = prevDate )
    )
VAR selRegion =
    VALUES ( 'Order Value_Contact'[Region/Country] )
RETURN
    CALCULATE (
        DIVIDE (
            CALCULATE ( [Average CY], [CW/Year] = currCW )
                + CALCULATE ( [Average CY], [CW/Year] = prevCW ),
            2,
            -1
        ),
        FILTER (
            ALL ( 'Order Value_Contact' ),
            'Order Value_Contact'[Region/Country] IN selRegion
        )
    )

Regards,

Xiaoxin Sheng