Total Sales YTD for non-standard date table

[Nurry90]

Hi All,

I have the below DAX calculations for a non-standard date table calculation, for YTD, LY and 2 years ago. I have tried, but I cannot seem to find a way of totaling these. Someone mentioned grouping the dates, but not sure how to do this

. Also any help on making my Sales YTD formula more dynamic, instead of having to change the year every time would be great (The same formula format on the others, wont work on YTD for me)

Sales YTD = CALCULATE([Total Sales],
FILTER(‘Sellthrough Files’, ‘Sellthrough Files’[Fin Yr]=2021))

PreviousYearSales =
VAR CurrentFinWeek = MAX(‘Date Table’[FY Week number])
VAR CurrentFinYear = MAX(‘Date Table’[Fin Yr])
RETURN
CALCULATE([Total Sales],
FILTER(ALL(‘Date Table’),
‘Date Table’[FY Week number]=CurrentFinWeek &&
‘Date Table’[Fin Yr]=CurrentFinYear -1))

Previous2YearsSales =
VAR CurrentFinWeek = MAX(‘Date Table’[FY Week number])
VAR CurrentFinYear = MAX(‘Date Table’[Fin Yr])
RETURN
CALCULATE([Total Sales],
FILTER(ALL(‘Date Table’),
‘Date Table’[FY Week number]=CurrentFinWeek &&
‘Date Table’[Fin Yr]=CurrentFinYear -2
))

[Melissa]

Hi @Nurry90,

@Greg has put together a great article with pretty much all you need to know in regard to this topic, it also covers calculating totals - you can find that here.

Non-Standard Time Intelligence

The available DAX date intelligence functions in Power BI handle the comparison with previous periods easily for the standard periods of year, quarter, month, and day when using a standard calendar, or a standard fiscal calendar (where quarters start on a 3-month cadence, e.g., April 1, July 1, etc., and aligned to [but offset from] calendar quarters). These functions do not, however, handle weekly comparisons or custom fiscal calendars. Calculation techniques to handle week comparisons and non-…

.
If you need further assistance please provide a sample PBIX

[EnterpriseDNA]

Thanks for posting your question @Nurry90. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[EnterpriseDNA]

Hi @Nurry90, we’ve noticed that no response has been received from you since the 16th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

[EnterpriseDNA]

Hi @Nurry90, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[Nurry90]

Hi @Melissa,

Apologies for the late response. I have used the above post from @Greg, and it is partially working for some weeks. See below. Notice from FY2020 - WK12 to FY2020- WK27 the “Total Last Week” is not following the formula. And suggestions what this could be?

Also, (see bottom screenshot) I do not have full FY2019 data loaded yet, however the “Total Last Week” sales are showing up.

I have used this formula for the "Total Last WEek"calculation,
total last week =
VAR _ListOfWeeks = SELECTCOLUMNS(
ADDCOLUMNS(
VALUES( ‘Date Table’[FYWeeknYear] ),
“@LW”, CALCULATE(
MAX( ‘Date Table’[FYWeeknYear] ),
FILTER(
ALL( ‘Date Table’ ),
‘Date Table’[FYWeeknYear] < EARLIER( ‘Date Table’[FYWeeknYear] ) )
)
),
“Weeks”, [@LW]
)
VAR _Result = SUMX(
FILTER(
ALL( ‘Date Table’ ),
‘Date Table’[FYWeeknYear] IN _ListOfWeeks ),
[Total Sales]
)

RETURN
_Result

I am really struggling with these time comparison calculations on the non-standard date table Any help is greatly appreciated.

[Melissa]

Hi @Nurry90,

Please provide a sample PBIX, thanks.

[Nurry90]

Hi @Melissa,

I have done up a mock file. See attached. Let me know if you can view.

Sample PBIX file EnterpriseDNA.pbix (3.7 MB)

[Melissa]

Hi @Nurry90,

So I had a look at the attribute that you are iterating over:

And the issue is this piece of M code

InsertWeeknYear= 
    Table.AddColumn( InsertHiddenSortFYMonthID, "FYWeeknYear", each 
        [Fin Yr]*1000 + [FY Week number]*100,
        Int64.Type
    ) 

Changed that into

InsertWeeknYear= 
    Table.AddColumn( InsertHiddenSortFYMonthID, "FYWeeknYear", each 
        [Fin Yr]*10000 + [FY Week number]*100,
        Int64.Type
    ) 

and all is working properly

I hope this is helpful

[Nurry90]

You are amazing!!!

Thank you so much, you would not believe the amount of time I have spent trying to figure what was wrong with this.

Thanks again!