topic Calculate Number of Units Per Month Based on Start and End Dates in DAX Commands and Tips

Calculate Number of Units Per Month Based on Start and End Dates

jmarciogsousa — Sat, 14 Sep 2024 14:00:29 GMT

Hi,

Use case: Count the number of cars available per month based on a start and end dates.
Model: The model is simple one calendar and another fact table without relationship.

Dax Formulas: I have achive this with 2 formulas, one is correct but with bad performance another total is not correct for totals but the performance is good:

Pbix File: File with Example
Could you support me to achive the correct value with good performance.

Re: Calculate Number of Units Per Month Based on Start and End Dates

tharunkumarRTK — Sat, 14 Sep 2024 14:01:00 GMT

jmarciogsousa

You can try this formula:

_Cars_PerMonthAndDay = Var __Result = SUMX( 'Calendar', Var __Date = 'Calendar'[Date] return COUNTROWS( FILTER( Cars, Cars[ValidFrom] <= __Date && Cars[ValidUntil] >= __Date ) ) ) RETURN __Result

In my observation, the results are accurate and it is performing better than your version.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Re: Calculate Number of Units Per Month Based on Start and End Dates

ThxAlot — Sat, 14 Sep 2024 19:51:49 GMT

_Cars_PerMonthAndDay = SUMX( Cars, VAR __from = Cars[ValidFrom] VAR __until = Cars[ValidUntil] RETURN CALCULATE( COUNTROWS( 'Calendar' ), 'Calendar'[Date] >= __from, 'Calendar'[Date] <= __until ) )