Total Sales YTD for non-standard date table

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

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

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.

.
If you need further assistance please provide a sample PBIX

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.

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!

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!

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

Hi @Nurry90,

Please provide a sample PBIX, thanks.

1 Like

Hi @Melissa,

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

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=
[Fin Yr]*1000 + [FY Week number]*100,
Int64.Type
)
``````

Changed that into

``````InsertWeeknYear=
[Fin Yr]*10000 + [FY Week number]*100,
Int64.Type
)
``````

and all is working properly