Date Between does not seem to be working

DAX DAX Calculations
1

I hope I can find some help to a problem that has been vexing me for the last few hours.

I have a simple sales and date table combined and I want to create a baseline from the sales table to do some scenario planning on.

The data starts from April 2018 and the Fiscal year runs from April to March.

I created measures to segment the income into different channels using a simple filter.

I then created a measure for going back two years using date add to create my baseline. The logic for this is that my scenario starts from April 2021 and I wanted a stable year to model from. If I took last years data i.e 2020 it has been a little different from the norm because of coranavirus!

This means that I now have baseline data starting from April 20 onwards (Up to Jul 22 as I write this).

I wanted to reduce the dataset to a single year so I filtered my baseline on FY as follows

Income Baseline = CALCULATE(

[Income L2Y £k], FILTER(Dates,Dates[FY] = “FY22”))

This has worked fine and given me 12 months of data in April 2021 to March 2022. The annoying thing is that March 2022 is not a great number as it was impacted by the start of coranavirus. I have therefore tried to adjust the baseline to start on 1st March 2021 and end 28th Feb 2022 (Equiv of using March 19-Feb 19).

Adjusted Income Baseline = CALCULATE(

[Income L2Y ÂŁk],

DATESBETWEEN(

Dates[Date],

DATE(21,03,01),

DATE(22,02,28)))

When I do this I get no data in my visualisation (no errors though). I definitely have data in those time periods. I have tried using a filter with greater than and less than and no luck there. I don’t understand how it calculates with a single value like fiscal year but not with date between.

Can anyone provide pointers on where I am going wrong here?

2

Just at first glance, but the DATE function requires DATE ( Year, Month, Day). Try changing to 2021 and 2022 in your DatesBetween and see what happens.

1 Like
3

I am going to kick myself so hard if that’s it. Makes absolute sense but never once crossed my mind. I am away for weekend now but will check as soon as I get back on Monday. Thanks for your help.

4

Hi @AllanL, 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. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

So the DateBetween function actually didn’t give me what I was after as it returned the total of that period but pointing out the schoolboy error I was doing with the year in the Date function meant that I could just filter with greater and less than and get what I needed… Thx very much