Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This might not be as diffifult as I think, but I am having trouble to get around it.
I have a table that consist of cases: one row per case. Each case has a start date and an end date. I have added a quantity column for clarity so that I can use SUM instead of COUNT.
What I need to do is to calculate ONGOING cases at the end of the chosen month. i.e where start date is on or before the last day in the chosen month, and with an end date that is AFTER the last day in the chosen month.
It was easy enough to create a test measure for a specific month, but I am not able to make it dynamic to work with year and month filter/slicer.
Any suggestions?
Here's my test measure:
TEST_OngoingCases_dec2018:=
CALCULATE(SUM(FactCase[Quantity]);FactCase[RegDate_FK]<=20181231;FactCase[ClosingDate_FK]>20181231)
can u please post some sample data and expected results
Se pictures for sample data. I have loaded these tables into my Power BI (only two of several as part of my model). Datetable is loaded twice, once as RegDate and once as ClosingDate.
What I want to do is to be able to use the year and month from one of the date tables to filter 'Ongoing cases' measure. If i choose Jan 2018 I want to see the sum of all cases with a regdate on or before 31st of jan AND with a closing date after 31st jan. Same for feb, march, april etc.
hi, @Anonymous
You may try this measure
Measure = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[RegDate ]<=MAX('Date'[Date])&&Table1[ClosingDate]>MAX('Date'[Date])))
Note: Do not create the relationship between data table with date table
this is based on the year and month you just select one value.
If you select two or more the year and month, what is the logic ?
For example:
If you choose Jan 2018 and Feb 2018 .
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |