Calculate event days included in in this month

[WorkHard]

Say I have an event with a start date of 2/26/2020 and an end date on 3/2/2020.

To calculate the duration of the event for this month, how would I only count the February days (4 days instead of 6 days) ?

If I create a DAX formula, I can only count (End Date) - (Start Date) = Duration.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

You can try like this. In case you want to count all days you do a count of the date instead of the sum of the workday. I used a date calendar

Measure = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StartDate] && Dim_Date[Calendar_Date]<= Table2[EndDate]),Dim_Date[Calendar_Date],Dim_Date[Workday)]),Dim_Date[Workday],CROSSFILTER(Dim_Date[Calendar_Date],Table2[StartDate],None))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak]

One like this

CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StartDate] && Dim_Date[Calendar_Date]<= Table2[EndDate]),Dim_Date[Calendar_Date]),Dim_Date[Calendar_Date]),CROSSFILTER(Dim_Date[Calendar_Date],Table2[StartDate],None))

[WorkHard]

Hm, thanks @amitchandakthis works as a measure but I'm not sure how do you account when I filter the dataset for a particular month. How does this calculate and figure out which month to count the days in?

Additionally, it looks like the Total is not calculated correctly. The individual rows have the correct calculation but the total is much much less than the actual sum.

[amitchandak]

Can you share sample data and sample output. and mark me @

[az38]

Hi @WorkHard 

it depends on your data model and desired output but try a column like this

Column = 
var _tbl = filter(calendar([Start Date];[End Date]);MONTH([Date])=Month(today()))
return
calculate(countrows(_tbl))

[Tad17]

Use the FILTER function to filter to the MONTH of the first date in your range:

FILTER: https://docs.microsoft.com/en-us/dax/filter-function-dax

MONTH: https://docs.microsoft.com/en-us/dax/month-function-dax

If this helps please kudo.

If this solves your problem please accept it as a solution.