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
pistachio
Helper I
Helper I

Daily graph context (Variables), Calculating "open in period" measure

Building on PowerPivotPro's "Active Promotions pattern" I have tables of accounts which have:

  • An opening Date
  • A closing Date
  • A status (1 being open right now.) Once an account is closed the closing date is filled (otherwise it's empty), and the status is changed)

I am looking to create a graph showing the number of accounts open each day. I have a date hierarchy on my date column. Putting this on my X axis with only "Month" selected seems to create the roughly correct figures but if I add "Date" and "Month" to the date hierarchy (so each data point represents a day) the result is too small, like it is only counting accounts which are open in the present day and were open then (in the past context).

 

Here is my current measure:

maxDate = MAX(DimDate[Date])

# Loans Open = 
VAR EndPeriod = [maxDate]
Return
    CALCULATE(
	      DISTINCTCOUNT(DimAccount[Pk]), 
			FILTER(
				DimAccount,
				DimAccount[DateOpened] <= EndPeriod
                                && OR(DimAccount[DateClosed] >= EndPeriod, DimAccount[status] = 1)
                                && DimAccount[ProductTypeId] <> "D"
				) 
			) 
2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @pistachio ,

To understand your scenario better, could you share some data sample with table format and your desired output so that we could copy and test on it?

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, thanks for the reply. I've simplified the data and re-posted here:

https://community.powerbi.com/t5/Desktop/Daily-graph-context-Variables-Calculating-quot-open-in-peri...

 

thanks, 

 

Tom

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.