topic Re: DAX to understand period between start and end date in DAX Commands and Tips

DAX to understand period between start and end date

Anonymous — Fri, 01 Nov 2024 11:41:06 GMT

Hi,
I need your professional help.

I have built up a savings table for my team which looks like attached.

I have been trying to build up a Dashboard on PowerBi (attached).

What I want is to have a DAX which enables to calculate the savings of a period, by taking into consideration the start and end date on my table.

On all models I have done, it always consider the full period savings, and not the period within.

For example, If i select a period on Power BI from Jan 2024 to Jun 2024, it should show a total of 720,000 (line 1) + 330,000 (line 2)
The problem is that it doesn't understand these periods between start and end.

I know it is possible to do it (because someone already did it for me 2 years ago), but I just can't find a way to do it myself.

Your help will be much appreciated 🙂

Thanks

Re: DAX to understand period between start and end date

Greg_Deckler — Fri, 01 Nov 2024 12:59:50 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: DAX to understand period between start and end date

Anonymous — Mon, 04 Nov 2024 09:04:50 GMT

Thanks for the reply from Greg_Deckler , please allow me to provide another insight:
Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure = var _mindate=MINX(ALLSELECTED('Table 2'),'Table 2'[Date]) var _maxdate=MAXX(ALLSELECTED('Table 2'),'Table 2'[Date]) return SUMX( FILTER('Table', 'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[Amount])

2. Result:

If dax gets results that don't match your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly