DAX formula

[Margreet]

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:

[Anonymous · Accepted solution]

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

[amitchandak]

@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-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Margreet]

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!

[Margreet]

Great, thank you! I will try working this.