Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mwallace001
Frequent Visitor

Issues with date measures and filters

Hi awesome people,

 

I'm having a really hard time with a report I'm building to check outcomes on allied health cases. This report has 3 measures, Commencements, Placements and Sustainable Outcomes. the bit that is killing me is the dates. I have a separate date table which links to the invoice dates in our invoicing table.  The date measures I need are:

  1. Report Date - the date this report is run for our regulator, is quarterly

  2. Earliest Date - this is when the commencements count from, eg. EOMONTH([Report Date,-19)

  3. Latest Date - this is the last date commencements can count from eg. EOMONTH([Report Date,-10)

 

The measures on our quality then become

  1. Commencements =CALCULATE(DISTINCTCOUNT(Invoice Table[Case Number]),

  FILTER(Invoice Table,Invoice Table[ChargeCode]="X"
  && Invoice Table[Date of Invoice]>=[Earliest Date]
  && Invoice Table[Date of Invoice]<=[Latest Date]))
  2. Placements =CALCULATE(DISTINCTCOUNT(Invoice Table[Case Number]),
  FILTER(Invoice Table,Invoice Table[ChargeCode]="Y"
  && Invoice Table[Date of Invoice]>=[Earliest Date]
  && Invoice Table[Date of Invoice]<=[Report Date]))
  3. Sustainable Outcomes =CALCULATE(DISTINCTCOUNT(Invoice Table[Case Number]),
  FILTER(Invoice Table,Invoice Table[ChargeCode]="Z"
  && Invoice Table[Date of Invoice]>=[Earliest Date]
  && Invoice Table[Date of Invoice]<=[Report Date]))
 
I think my problem is the Report Date measure, at the moment its MAX(Date Table) then I use it as a slicer to pick the one date for the report - eg 31/12/2018. I can see in my table the Earliest Date and Latest Date measures work, but the Commencement / Placement / Sustainable Outcomes measures no longer work. I'm wondering if this is because it's now looking for only invoices with that one Report Date that fit the slicer, but I'm not really sure how to get around this.
 
Any suggestions on how to get the right ranges which so many dependencies on dates in my measures?
 
Thanks,
Michelle
4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @mwallace001 ,

Firstly, I have a little confused about your scenario based on your description.

As we know, measure cannot be used as a slicer in power bi currently. So how do you use Report Date measure as a slicer?

To understand your scenario better, I need to know more about details.

If it is convenient, please share a dummy pbix file which can reproduce the scenario and your desired output, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

I've created some really basic dummy data to try and illustrate what I'm doing, please see the link below.  The excel file has a tab called "Expected Outcome" showing what I'm trying to achieve.

https://myacuedu-my.sharepoint.com/:f:/g/personal/s00231990_myacu_edu_au/EuUTElGqz7VLm58Zl1MLQB4Btox...

 

Thanks so much for your responding and thanks in advance for your help.

 

Michelle

Hi @v-piga-msft , any thoughts on the dummy data?

@v-piga-msft , any suggestions?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.