cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX Measure calculating values for each day within a period

Require assitance please:

 

FactTable

CourseDelegateStart dateEnd date
a123a2020/01/062020/01/10
a123b2020/01/062020/01/10
b234f2020/01/072020/01/07
b234y2020/01/072020/01/07
b567z2020/01/072020/01/10

 

DimDate is other table 

 

Looking for this solution:

Course2020/01/062020/01/072020/01/082020/01/092020/01/10Total
a1232222210
b234 2   2
b234 11114
Total2533316


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

 

Annotation 2020-07-11 110643.png

Highlighted
Community Champion
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.

 

Annotation 2020-07-11 162928.png

 

 

 

 

Highlighted
Community Champion
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
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
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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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

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.