cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE New Contributor
New Contributor

Creating an 'Open' Measure to show if something is open against a date period e.g month

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi @DebbieE 

 

check this out.

 

PBIX

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

View solution in original post

Super User III
Super User III

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi @DebbieE 

 

check this out.

 

PBIX

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

View solution in original post

Super User IV
Super User IV

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User IV
Super User IV

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DebbieE New Contributor
New Contributor

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi, thanks for the response. This one doesnt work because I have a start and and end date not single date.

DebbieE New Contributor
New Contributor

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi, Unfortunately I cant use this one either because I dont have an Open date. i have a start date and an end date

Super User IV
Super User IV

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi,

I do not know whom you are replying to but my solution should work.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DebbieE New Contributor
New Contributor

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

mwegener the solution isnt quite working. 

 

ErrorwithOpenMeasure.JPG

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

Super User III
Super User III

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

Hi @DebbieE,

 

what do you wish?

Should March still be displayed as open?

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

DebbieE New Contributor
New Contributor

Re: Creating an 'Open' Measure to show if something is open against a date period e.g month

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors