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

[jmarciogsousa]

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.

[tharunkumarRTK · Accepted solution]

@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!

[ThxAlot · Accepted solution]

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

[tharunkumarRTK · Accepted solution]

@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!