cancel
Showing results for
Did you mean: Frequent Visitor

Hi.
I have the following example table:

 id opening date closing date 1 01/01/2021 01/02/2021 2 01/01/2021 01/02/2021 3 01/01/2021 01/02/2021 4 01/01/2021 01/02/2021 5 01/01/2021 6 01/01/2021 7 01/01/2021 8 01/01/2021 9 01/02/2021 10 01/02/2021 11 01/02/2021 12 01/02/2021 13 01/02/2021 14 01/02/2021 15 01/02/2021 16 01/02/2021 17 01/03/2021 18 01/03/2021 19 01/03/2021 20 01/03/2021

Each row of the table is a task (demand) that has been generated for someone. The objective is to create a histogram that shows the amount of accumulated open demands. An open demand is one that does not have an closing date, or whose closing date is later than the month bar in the histogram. The result should be something like: *i know this is not a histogram

Note that 8 demands were generated in January, 8 in February and 4 in March, but 4 were closed in February.

Could someone help me create this chart?

1 ACCEPTED SOLUTION  Super User

@FranciscoHoff  you can create a measure like this

``````Measure =
VAR _filter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
)
VAR _opening =
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
)
VAR _closing =
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
)
VAR _accumulatedOpening =
CALCULATE (
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
),
_filter,
demand
)
VAR _accumulatedClosing =
CALCULATE (
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
),
_filter,
demand
)
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )
`````` Proud to be a Super User!

New Animated Dashboard: Sales Calendar

3 REPLIES 3  Super User

@FranciscoHoff  you can create a measure like this

``````Measure =
VAR _filter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
)
VAR _opening =
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
)
VAR _closing =
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
)
VAR _accumulatedOpening =
CALCULATE (
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
),
_filter,
demand
)
VAR _accumulatedClosing =
CALCULATE (
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
),
_filter,
demand
)
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )
`````` Proud to be a Super User!

New Animated Dashboard: Sales Calendar Frequent Visitor

I know this is not a question directly related to the post, but could you help me understand some details of your code? I'm new to the platform and haven't had much contact with DAX.

1) In the FILTER function of the _filter variable, how does the second part of the filter parameter after "&&" not return a month greater than a month from another year that is not the current context year? For example, if it has already processed all the months of 2020, how does it not return the month of December for all entries for the year 2021?
2) Could the CALCULATE nested in the _accumulatedOpening and _accumulatedClosing variable be replaced by the _opening and _closing variables?
3) Why is the IF clause at the end necessary? Frequent Visitor

worked perfectly! The only problem I had was that my original data was in datetime, so I needed to convert it to date.

Thanks!!! Announcements The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform. #### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison! #### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022. Top Solution Authors
Top Kudoed Authors
Users online (1,462)