Same day and same week last year not showing correct totals

[Powereports]

Hi Users,

 I looking to obtain the sum of billed amount for same day and same week last year. I have a calculated weeknumber and day number column in my Calendar table. My current issue is that I am able to obtain the billed amount for a specific day for same week last year , but the total for the entire week is not summing up correctly. Ideally we should be getting 92290001.68   (totals value of  SWLY) in the totals for Same week and same day last year but currently it gives 853,888.00 as in screenshot below.

Negative Retention for entire time period

Below are the measures used for calculation:

1. Billed Amount This Year = ROUNDUP(sum ('Billed Amount'[Amount Billed]), 2)

2. Same week same day last year =

Var CurrentYear = IF (

HASONEVALUE('Calendar'[Year]),

VALUES ('Calendar'[Year])

)

Var CurrentWeek = IF (

HASONEVALUE('Calendar'[WeekNum]),

VALUES ('Calendar'[WeekNum ])

)

Var CurrentWeekDay = IF (

HASONEVALUE('Calendar'[Day of Week]),

VALUES ('Calendar'[Day of Week])

)

Var Result =

CALCULATE('Billed Amount'[Billed Amount This Year],

ALL ('Calendar'),

'Calendar'[ WeekNum ]= CurrentWeek,

'Calendar'[Year] = CurrentYear -1 ,

'Calendar'[Day of Week] = CurrentWeekDay

)

Return Result

3.SWLY = CALCULATE ('Billed Amount'[Billed Amount This Year],

FILTER (ALL('Calendar'),

'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year])-1 &&

'Calendar'[WeekNum]= SELECTEDVALUE ('Calendar'[ WeekNum])))

Thank you in advance!

[v-cazheng-msft]

Hi @Powereports 

You can make some changes to your formula.

Same week same day last year =

VAR CurrentYear =

    IF ( HASONEVALUE ( 'Calendar'[Year] ), VALUES ( 'Calendar'[Year] ) )

VAR CurrentWeek =

    IF ( HASONEVALUE ( 'Calendar'[WeekNum] ), VALUES ( 'Calendar'[WeekNum ] ) )

VAR CurrentWeekDay =

    IF (

        HASONEVALUE ( 'Calendar'[Day of Week] ),

        VALUES ( 'Calendar'[Day of Week] )

    )

VAR Result =

    CALCULATE (

        'Billed Amount'[Billed Amount This Year],

        ALL ( 'Calendar' ),

        'Calendar'[ WeekNum ] = CurrentWeek,

        'Calendar'[Year] = CurrentYear - 1,

        'Calendar'[Day of Week] = CurrentWeekDay

    )

VAR SWLY =

    CALCULATE (

        'Billed Amount'[Billed Amount This Year],

        FILTER (

            ALL ( 'Calendar' ),

            'Calendar'[Year]

                = SELECTEDVALUE ( 'Calendar'[Year] ) - 1

                && 'Calendar'[WeekNum] = SELECTEDVALUE ( 'Calendar'[ WeekNum] )

        )

    )

RETURN

IF ( HASONEFILTER ( [Date] ), Result, SWLY )

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powereports]

Hi Caiyun ,

 Thanks for the tweaking the formula to show correct totals it helps!1 But i am still facing a problem using th above formula you suggested in the line chart as below. As in the below screenshot, the line chart shows just day wise trend for the current year but the there is no day wise trend for the previous year (highlighted in yellow). It just shows the total value for the entire week even on the day wise trend.

Any suggestions to make the formula work to show weekly as well as daily totals for previous year on a line chart?

Screenshot:

Thanks in Advance for your help!!

[v-cazheng-msft]

Hi @Powereports 

I'm sorry that the screenshot you provided is too fuzzy, I can't see it clearly. But I can see that you use the WeekNum column and Day column instead of the Date column as the X axis. The axis you choose determines how Measure is calculated. You can make some changes to your formula. If it doesn't work, please provide some sample data without sensitive data.

IF ( HASONEVALUE ( [Date] ), Result, SWLY ) or IF ( HASONEFILTER ( [Day] ), Result, SWLY )

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powereports]

Hi Caiyun,

 I tried substituting the above in the formula and it doesn't give the results.

 I am looking at trying to get the billed amount same week and same this year VS previous year and same week same day this year VS previous year in the line chart on drill down.

Please find the attached pbix file in one drive from the link.

https://onedrive.live.com/?id=CD2EC557668BFEBD%21130&cid=CD2EC557668BFEBD

Thank you in advance!!

[v-cazheng-msft]

Hi @Powereports 

Sorry, I cannot access the pbix file you shared. Do you have permission to upload files in Community? You can directly drag the file to the attachment area and upload it. Or you can check the status and permission of the link.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powereports]

I am unable to post the pbix directly in the community site here.

 Would you mind sending your emailId, so that i could give access in one drive or email the PBix file?

[v-cazheng-msft]

I'm sorry but we are not allowed to provide email information and support customer by other ways. You can try other ways to share it and make sure it can be downloaded. 

Best Regards

Caiyun Zheng

[Powereports]

Sure Caiyun,

 That makes sense,I was just thinking if i could give access to you through the your emial Id to Onedrive.

Can you please let me know if you can access the file here through dropbox url below.Also, if you are unable to access can you let me know other modes to attach the file here?

https://www.dropbox.com/home?preview=Daily+Sales+Trends+totals+not+showing+correctly.pbix

[v-cazheng-msft]

Hi @Powereports 

Sorry for late reply. You just need to make small changes to the SWLY variable in the Measure.

VAR SWLY =

    CALCULATE (

        'Billed Amount'[Billed Amount This Year],

        FILTER (

            ALL( 'Calendar' ),

            'Calendar'[FiscalYear]

                = SELECTEDVALUE ( 'Calendar'[FiscalYear] ) - 1

                && 'Calendar'[Fiscal WeekNum Icey]

                    = SELECTEDVALUE ( 'Calendar'[Fiscal WeekNum Icey] )

        ),VALUES('Calendar'[Day])

    )

The result looks like this:

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-cazheng-msft · Accepted solution]

Hi @Powereports 

Unfortunately, I can't reach this link as well. Could you please change the link of your first link to 'Anyone with the link can edit' If your data does not contain sensitive information?

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powereports]

Thanks Caiyun.

Works great . Thank you for the small tweak in the Dax.

[Powereports]

Hi Caiyun,

 I guess you should be able to access the file file through the link bleow as i have edited the permissions to anyone can edit

https://1drv.ms/u/s!Ar3-i2ZXxS7NgQM6uEz0ppvwt6Mp?e=fNOgWY 

Please let me know if you cannot still access it.

 Thanks, 

Ragini

[v-cazheng-msft]

@Powereports 

Thanks, I've downloaded it successfully!

Best Regards 

Caiyun Zheng