Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Im just starting to think about how to display the following
Start Date End Date Fact
A 05/02/2019 06/09/2019 1
B 20/04/2019 03/05/2019 1
C 01/05/2019 29/05/2019 1
Obviously if you want to show by Start date you would get the following
Feb 19 Apr 19 May 19
A 1
B 1
C 1
But what happens you want to display that its open between start and end dates ?
Feb 19 May 19 Apr 19 May 19 Jun 19 Jul 19 Aug 19 Sep 19 Oct 19
A 1 1 1 1 1 1 1 1 1
B 1 1
C 1
Is there an easy way of setting this up as a dax measure so you can create an 'open' Measure to use?
Solved! Go to Solution.
Hi @DebbieE
check this out.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @DebbieE ,
try this:
Fact Start =
CALCULATE(
SUM('Table'[Fact]);
FILTER(
ALLSELECTED('Date'[Date]);
'Date'[Date] <= MAX('Date'[Date])
)
)
Fact End =
CALCULATE(
SUM('Table'[Fact]);
FILTER(
ALLSELECTED('Date'[Date]);
ENDOFMONTH('Date'[Date]) < MAX('Date'[Date])
);USERELATIONSHIP('Date'[Date];'Table'[End Date])
)
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, thanks for the response. This one doesnt work because I have a start and and end date not single date.
Try something like this
calculate(count(claims[id]),filter(claims,claims[Open Date]<ENDOFMONTH('Date'[Date Filer]) && claims[Close Date]>ENDOFMONTH('Date'[Date Filer]) )
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi, Unfortunately I cant use this one either because I dont have an Open date. i have a start date and an end date
Hi,
I do not know whom you are replying to but my solution should work.
Hi @DebbieE
check this out.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
mwegener the solution isnt quite working.
This account is open from Januar through to March.
However in March, the open flag isnt being set because
If Account Start - Account End = 0 Then put nothing Else Account Start - Account End
January 1 - 0 = 1 So its set as 1
February 1 - 0 = 1 So its set as 1
March 1-1 = 0 so its set as not open
I dont know how to stop the Start Date Measure in March using the DAX you sent to me
This specific account closed on the 12st of March so it should be open in March and fully closed in April.
Ive tried to amend the measures slightly but Ive not been able to figure out how to do it.
I understand the Open DAX but Im struggling to get my head around the Fact_Start and Fact_End Dax
Hi @DebbieE ,
try this:
Fact Start =
CALCULATE(
SUM('Table'[Fact]);
FILTER(
ALLSELECTED('Date'[Date]);
'Date'[Date] <= MAX('Date'[Date])
)
)
Fact End =
CALCULATE(
SUM('Table'[Fact]);
FILTER(
ALLSELECTED('Date'[Date]);
ENDOFMONTH('Date'[Date]) < MAX('Date'[Date])
);USERELATIONSHIP('Date'[Date];'Table'[End Date])
)
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
It worked.
Thank you so much
Now all I need to do is try and understand how its all working which is a bit daunting.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |