topic Re: DAX formula in DAX Commands and Tips

DAX formula

Margreet — Tue, 15 Nov 2022 21:48:37 GMT

Hi, I got a simple model only a date table and a table with Nr, StartDate, Enddate.

The date from datetable is connected to Startdate.

In the visualisatie I want to see a 1 in every quarter from start to end date.

For example nr 1 with startdate 1-2-2021 and enddate 1-8-2022. What for formula in Dax works?

Thank for reaction.

I want to see this:

Re: DAX formula

amitchandak — Wed, 16 Nov 2022 02:35:49 GMT

Margreet , I think these blogs can help

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: DAX formula

Margreet — Wed, 16 Nov 2022 08:23:38 GMT

Great, thank you! I will try working this.

Re: DAX formula

Margreet — Wed, 16 Nov 2022 12:51:53 GMT

Hello, I tried now this:

Count_active_in_period = CALCULATE(COUNTx(FILTER('Table','Table'[StartDate]<=max(Datetable[Date]) && 'Table'[Enddate]>max(Datetable[Date])),('Table'[nr])),CROSSFILTER('Table'[StartDate],Datetable[Date],None))

In 2021_Q2: I want to see 2; this is nr 1 and nr 3 are active in that period.

Hope some know what I can do in the formule, thanks a lot!

Re: DAX formula

Anonymous — Mon, 21 Nov 2022 02:23:28 GMT

Hi Margreet,

It seems like a common multiple date fields analysis requirement, you can take look at the following blog start date end date part if it helps:

Before You Post, Read This

In addition, you can also try to create a calculated table to expand these date ranges and use it as bridge to mapping raw table records:

Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community

Regards,

Xiaoxin Sheng