powerbi dax datesbetween

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Power BI training and help » PowerBi DAX - DATESBETWEEN

PowerBi DAX - DATESBETWEEN

resolvedResolved · Urgent Priority · Version 365

Amy has attended:
Power BI Modelling, Visualisation and Publishing course

PowerBi DAX - DATESBETWEEN

Hello,

I have calculated a measure which shows the values of activity between two dates, syntax below:

Total Activity forecast = calculate([Total Activity Forecast 2022/23 For Graph], DATESBETWEEN('Dim_SUS Forecast Dates'[Date],date(2022, 06, 01), date(2023, 03, 31)))

This brings back the value that I want. However, I want to change the datesbetween section of this measure to be interactive, rather than hard coded dates.

I have tried a few different options, but neither appear to work.

1) I have created variables within the measure itself:

Total Activity forecast amount this year =
Var ForecastStartDate = calculate(max('APC SUS'[DateConcatenated - Date]), DATEADD('APC SUS'[DateConcatenated - Date], 1, month))
Var FinYearEndDate = calculate(MAX(FinancialYearDates[Date]))
Return
calculate([Total Activity Forecast 2022/23 For Graph], DATESBETWEEN('Dim_SUS Forecast Dates'[Date], ForecastStartDate, FinYearEndDate))

2) I have created measures in the table and then used these in the measure in the datesbetween function:

Total Activity forecast amount this year = CALCULATE([Total Activity Forecast 2022/23 For Graph], datesbetween('Dim_SUS Forecast Dates'[Date], [ForecastStartDate], [FinYearMaxDate2]))


Both of these options bring back a blank row, rather than the value. I've checked the data types and the measures for the dates themselves and they are bringing back the correct dates.

Are you able to help at all please? Deadline for the output is this week so if you could get back to me ASAP that would be amazing.

Thank you,

Kind Regards,

Amy

RE: PowerBi DAX - DATESBETWEEN

Hi Amy,

Thank you for the forum question.

Do you have the futures dates in the date table?

A good start is to debug the code:

Total Activity forecast amount this year =
Var ForecastStartDate = calculate(max('APC SUS'[DateConcatenated - Date]), DATEADD('APC SUS'[DateConcatenated - Date], 1, month))
Var FinYearEndDate = calculate(MAX(FinancialYearDates[Date]))
Return
calculate([Total Activity Forecast 2022/23 For Graph], DATESBETWEEN('Dim_SUS Forecast Dates'[Date], ForecastStartDate, FinYearEndDate))

Try to change it to:

Total Activity forecast amount this year =
Var ForecastStartDate = calculate(max('APC SUS'[DateConcatenated - Date]), DATEADD('APC SUS'[DateConcatenated - Date], 1, month))
Var FinYearEndDate = calculate(MAX(FinancialYearDates[Date]))
Var calculate([Total Activity Forecast 2022/23 For Graph], DATESBETWEEN('Dim_SUS Forecast Dates'[Date], ForecastStartDate, FinYearEndDate))
Return

ForecastStartDate

Add the measure to a Card and see if it returns a result. Debug the other variable. Change the code to:

Total Activity forecast amount this year =
Var ForecastStartDate = calculate(max('APC SUS'[DateConcatenated - Date]), DATEADD('APC SUS'[DateConcatenated - Date], 1, month))
Var FinYearEndDate = calculate(MAX(FinancialYearDates[Date]))
Var calculate([Total Activity Forecast 2022/23 For Graph], DATESBETWEEN('Dim_SUS Forecast Dates'[Date], ForecastStartDate, FinYearEndDate))
Return

FinYearEndDate

By debugging the two variables you can find out which of the variables give the problem or if both are wrong.

Let me know what suggested debug returns


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: PowerBi DAX - DATESBETWEEN

Hi Jens,

Thanks for replying so quickly.

I have the future dates in the Dim_SUS Forecast dates table, not in the APC SUS table.

I have tried this before (debugging that the dates in the measures are correct) and they pull through the right date in a short date format with the time. I have then changed the data type to be short date.

This still returns a blank cell when I try to return the calculation.

Do you have any ideas why this would be happening? Is the syntax correct to use the measures for start and end date rather than hard coded dates for the datesbetween function?

Thanks,

Kind Regards,

Amy

RE: PowerBi DAX - DATESBETWEEN

Hi Amy,

All time intelligence functions needs the primary key from the date table. Is the Dim_SUS Forecast dates table related to the transaction table (fact table)?

In DATEADD('APC SUS'[DateConcatenated - Date], 1, month) you have 'APC SUS'[DateConcatenated - Date] as the primary key and in DATESBETWEEN('Dim_SUS Forecast Dates'[Date], ForecastStartDate, FinYearEndDate) you have Dim_SUS Forecast Dates'[Date] as the primary key. It should be the same primary key.

I will need to see your data model to understand the logic.

Do you work with 2 date tables?

If you take a screenshot of the data model and send it to; info@stl-training.co.uk

I am sorry, but I will first have time Thursday to have a look at it.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: PowerBi DAX - DATESBETWEEN

Hi Jens,

The APC SUS table is my fact table. I then have a relationship between the Dim_SUS forecast dates table and the APC SUS table.

Dim_SUS forecast dates table is the primary key.

I have replicated the code above for the start date, relating to the APC SUS table in the dim_sus table as a measure, but the entry is still blank.

The value that I am trying to sum is the forecasted value that we went through in the session. I have adjusted the value so that if there is a value in the actual data, to show blank, and it seems like the calculation could be seeing the blank cells before getting to the datesbetween function and returning that?

This is the Total Activity Forecast 2022/23 For Graph calculation, that the above is trying to calculate in the date range:

Total Activity Forecast 2022/23 For Graph =
// Find the average of 2019/20 FYTD total activity and 2021/22 to find an average
Var LastYear=CALCULATE([Total Activity], PARALLELPERIOD('Dim_SUS Forecast Dates'[Date], -12, MONTH))
Var TwoYearsAgo=CALCULATE([Total Activity], PARALLELPERIOD('Dim_SUS Forecast Dates'[Date], -36, MONTH))
Return
//If there is an actual value, then blank, overwise show the forecast
if([Total Activity]=0,DIVIDE(LastYear+TwoYearsAgo,2,0),blank())



Thanks for your help,

I'll send across my data model.

Thanks,

Kind Regards,

Amy

RE: PowerBi DAX - DATESBETWEEN

Hi Jens,

I've changed the query to now show the values even if not blank in order to help debug it.

This brings back the calculation.

On further inspection, the start date is the one that's causing me the issue. It should be the 1st June 2022, not 1st May, so the date add function is not working.

Do you have any idea why this is?

Thanks again for your help,

Kind Regards,

Amy

RE: PowerBi DAX - DATESBETWEEN

Hi Amy,

DAX can be confusing.

Var LastYear=CALCULATE([Total Activity], PARALLELPERIOD('Dim_SUS Forecast Dates'[Date], -12, MONTH))

Imaging you have a matrix which shows [total activity] over years and months. If you add the measure above to the matrix, then the matrix will show the value from March 2021 as the value for March 2022. It takes the value from 12 month back in time.

If you to the same matrix add the measure NextMonth=Calculate([Total Activity],DATEADD('Dim_SUS Forecast Dates'[Date], 1, month), it will display [total activity] from April 2022 as the value for March 2022.

If what you want is to find a max date and then go one month forward you will need =EDATE(MAX('Dim_SUS Forecast Dates'[Date],1). If max date is 1 May 2022 then the EDATE will bring you to 1 June 2022.

The link below will explain Sameperiodlastyear, DateAdd, and Parallelperiods

https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question

Sorry I have no more time today.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

PowerBi DAX - DATESBETWEEN - Resolved

Hi Jens,

I have now solved the issue.

I created a new column, adding a month onto the APC sus date, then used the max of this for my calculations along with the new forecast measure without blanks.

For some reason, the blanks stop the calculation.

Thanks for your help!

Please also ignore my email re the data model.

Have a great week,

Kind Regards,

Amy

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI articles

Power BI tip:

Scheduled Refresh and Power BI Service

If you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints.

View all Power BI hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.