Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
coatsy35
Frequent Visitor

DAX formula to include count from certain months onwards in a previous year and add to YTD

I have the following problem, I have a large spreadsheet with counts of items sold YTD - year selected using a Year slicer. This all works fine. However, the recording window for one item has been changed to now be 1st July until 31st June instead of 1st Jan - 31st Dec like all the other sales.

 

How would I alter my DAX count code to look backwards and count not just this year as given by the slicer, but also count any Bus's sold from the 1st of the 7th month in the previous year? Sample File 

 

Screenshot 2024-02-22 143823.png

 

 

1 ACCEPTED SOLUTION
amustafa
Super User
Super User

Here's a DAX to account for your fisca year for Type = 'Bus'.

Adjusted Count of Unit Type = 
VAR CurrentYearStart = DATE(YEAR(TODAY()), 1, 1)
VAR CurrentYearEnd = DATE(YEAR(TODAY()), 12, 31)
RETURN
SUMX(
    'Table1',
    VAR SaleDate = 'Table1'[Sale Date]
    VAR SaleYear = YEAR(SaleDate)
    VAR SaleMonth = MONTH(SaleDate)
    VAR IsBus = 'Table1'[Unit Type] = "Bus"
    VAR AdjustedSaleDate = IF(IsBus && SaleMonth >= 7, DATE(SaleYear + 1, SaleMonth, DAY(SaleDate)), SaleDate)
    RETURN
        IF(AdjustedSaleDate >= CurrentYearStart && AdjustedSaleDate <= CurrentYearEnd, 1, 0)
)

 

amustafa_0-1708618100143.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
amustafa
Super User
Super User

See my Sample file and observe how you placed the newly calculated column in Values and Type as columns.

DAX formula to include count from certain months.xlsx





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi,

 

Obviously way more complicated than I thought. I have downloaded your file but the values are still not showing how I need them. Please see screenshot below as to what it should be showing when the slicer is selected to 2023 or 2024. Sorry I may have not been very clear. Thank you for your help so far, I've learnt quite a bit from this already. Still not sure how to get what I need though, if I don't select a year in the slicer the grand total row does give me the values for this year. However the table will be filtered by users to show results for different years, just the "Bus" total for each year needs to be offset backwards 6 months and stop forwards 6 months fo each year, as it has a different fiscal year to the rest of the values. 

 

Screenshot 2024-02-23 091652.png

I had hoped to do it with pure dax, but I've found another way to do it by adding in a helper column of which year the figures belong to. see below

 

Screenshot 2024-02-23 105732.png

 

coatsy35
Frequent Visitor

Thank you for that. Howver, I copied this into my sample file to try it, all it does is add up everything in 2024 to it says 6 buses were sold. Not sure whats not right. It was a direct copy and paste into a new measure, I've obviosuly done something wrong. See image below

 

Screenshot 2024-02-22 173729.png

amustafa
Super User
Super User

Here's a DAX to account for your fisca year for Type = 'Bus'.

Adjusted Count of Unit Type = 
VAR CurrentYearStart = DATE(YEAR(TODAY()), 1, 1)
VAR CurrentYearEnd = DATE(YEAR(TODAY()), 12, 31)
RETURN
SUMX(
    'Table1',
    VAR SaleDate = 'Table1'[Sale Date]
    VAR SaleYear = YEAR(SaleDate)
    VAR SaleMonth = MONTH(SaleDate)
    VAR IsBus = 'Table1'[Unit Type] = "Bus"
    VAR AdjustedSaleDate = IF(IsBus && SaleMonth >= 7, DATE(SaleYear + 1, SaleMonth, DAY(SaleDate)), SaleDate)
    RETURN
        IF(AdjustedSaleDate >= CurrentYearStart && AdjustedSaleDate <= CurrentYearEnd, 1, 0)
)

 

amustafa_0-1708618100143.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.