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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
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)
)``````

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

Proud to be a Super User!

4 REPLIES 4
Super User

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

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

Proud to be a Super User!

Helper I

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.

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

Helper I

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

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

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

Proud to be a Super User!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors