cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX Measure calculating values for each day within a period

FactTable

 Course Delegate Start date End date a123 a 2020/01/06 2020/01/10 a123 b 2020/01/06 2020/01/10 b234 f 2020/01/07 2020/01/07 b234 y 2020/01/07 2020/01/07 b567 z 2020/01/07 2020/01/10

DimDate is other table

Looking for this solution:

 Course 2020/01/06 2020/01/07 2020/01/08 2020/01/09 2020/01/10 Total a123 2 2 2 2 2 10 b234 2 2 b234 1 1 1 1 4 Total 2 5 3 3 3 16

Must be able to cover period where course extends over more than one week, month etc

In this case the measure must exclude weekends.

8 REPLIES 8
Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

Use the standard CROSSFILTER(...,..., none) approach to fill the gaps.

Highlighted
Frequent Visitor

## Re: DAX Measure calculating values for each day within a period

Thanks for the promt response.

I could do with more help in terms of this soulution.

I am relatively new to powerbi and need more detail.

Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

If you are new to Power BI this might be a bit of a steep learning curve.  Anyway, here's the first step of the solution.

Create an inactive relationship between the end date  and the date in the DimDate table.

The result will calculate the individual items properly.  For the Totals you will need different calculations.

Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

Here is an alternative version that allows you to keep the table relationships active and that gets the column totals right. Still needs work for the row totals. Bonus - added weekend filter too.

Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

@Anthony67   One more version. Now the row and column totals are correct but the overall total is not yet.  No idea how to change that...

``````Delegates2 =
var da= SELECTEDVALUE(DimDate[date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=min(FactTable[Start date]) && DimDate[date]<=max(FactTable[End date]) ),count(FactTable[Delegate]))
return if(HASONEVALUE(DimDate[date]),de,me)``````

I also don't know what happens if people start the same course at different times.

Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

ok, last version, promise!  I didn't get the grand total right but I managed to make the wrong one disappear. Good enough for me.

``````Delegates2 =
var da= SELECTEDVALUE(DimDate[date])
var sd= SELECTEDVALUE(FactTable[Start date])
var ed= SELECTEDVALUE(FactTable[End date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=sd && DimDate[date]<=ed ),count(FactTable[Delegate]))
return if(HASONEVALUE(DimDate[date]),de,me)``````
Highlighted
Community Champion

## Re: DAX Measure calculating values for each day within a period

Promises schmomises.  Here's the complete version including correct grand total.  I am pretty sure this can be optimized.

``````Delegates =
var da= SELECTEDVALUE(DimDate[date])
var sd= SELECTEDVALUE(FactTable[Start date])
var ed= SELECTEDVALUE(FactTable[End date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=sd && DimDate[date]<=ed ),count(FactTable[Delegate]))
var tt=ADDCOLUMNS(values(DimDate[date]),"ct",CALCULATE(count(FactTable[Delegate]),Filter(FactTable,FactTable[Start date]<=[date] && [date]<=FactTable[End date])))
return if(HASONEVALUE(DimDate[date]),de,if(HASONEVALUE(FactTable[Course]),me,sumx(tt,[ct])))``````
Highlighted
Frequent Visitor

## Re: DAX Measure calculating values for each day within a period

I am blown away by the effort you are making to assist me!

😀I dont mind you breaking your promises.
The solution is streats ahead of where I was but is still not returning the result I am looking for.

I am confident that it is something small or somthing I am miss applying.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors