Running Count Running Through Hierarchy

[Anonymous]

Hi Guys,

I thought I knew how to handle Dax, but now I am starting to get frustrated about solving the following Problem:

I want a Running Count for the concecutive Year/Quarter/Month/Day a Company generated Revenue. I already managed to Flag the result thourgh the Hierarchy with the Value 1 if its true and 0 if Not.

But the real challenge, where I'm struggeling right now, is to do a running Count on Consecutive Days/Month/Quarter/Years.
So far I haven't found a Way to achieve this.  The red numbers would be the desired result.

And here of course the Code for the simple_flag Measure:

Flag_simple =

 VAR varDate =
        VALUES ( DimDate[Date] )
    VAR varMonth =
        VALUES ( DimDate[MonthName] )
    VAR varQuarter =
        VALUES ( DimDate[QuarterName] )
    VAR varYear =
        VALUES ( DimDate[YearName] )
    VAR varDayUmsatz =
        SUMMARIZE ( 'Fact'; DimDate[Date]; 'Fact'[Umsatz] )
    VAR varMonthUmsatz =
        SUMMARIZE ( 'Fact'; DimDate[MonthName]; 'Fact'[Umsatz] )
    VAR varQuarterUmsatz =
        SUMMARIZE ( 'Fact'; DimDate[QuarterName]; 'Fact'[Umsatz] )
    VAR varYearUmsatz =
        SUMMARIZE ( 'Fact'; DimDate[YearName]; 'Fact'[Umsatz] )
    VAR varReturnValue = 0
    
    RETURN
        SWITCH (
            TRUE ();
            ISINSCOPE ( DimDate[Date] ); SUMX (
                varDate;
                IF ( CONTAINS ( varDayUmsatz; DimDate[Date]; DimDate[Date] ); 1; 0 )
            );
            ISINSCOPE ( DimDate[MonthName] ); SUMX (
                varMonth;
                IF ( CONTAINS ( varMonthUmsatz; DimDate[MonthName]; DimDate[MonthName] ); 1; 0 )
            );
            ISINSCOPE ( DimDate[QuarterName] ); SUMX (
                varQuarter;
                IF (
                    CONTAINS ( varQuarterUmsatz; DimDate[QuarterName]; DimDate[QuarterName] );
                    1;
                    0
                )
            );
            ISINSCOPE ( DimDate[YearName] ); SUMX (
                varYear;
                IF ( CONTAINS ( varYearUmsatz; DimDate[YearName]; DimDate[YearName] ); 1; 0 )
            );
            0
        )

Please let me know if you need more Information about my Problem.
I Thank you all in advanced for at leas giving me a hint how to do it, because so far I truly see no Chance of solving that.

Markus

[Mariusz]

Hi @Anonymous 

Please refer to the below, it's not a full solution but it should give you an idea.

Measure 15 = 
VAR __countDays = COUNTROWS( 'Calendar' )
VAR __countAllDaysInYear = COUNTROWS( CALCULATETABLE( 'Calendar', ALL( 'Calendar' ), VALUES( 'Calendar'[Year] ) ) )
VAR __countAllDaysInMonth = COUNTROWS( CALCULATETABLE( 'Calendar', ALL( 'Calendar' ), VALUES( 'Calendar'[Year Month] ) ) )
VAR __rankYear = RANKX( ALLSELECTED( 'Calendar'[Year] ), [Sales] )
VAR __rankMonth = RANKX( ALLSELECTED( 'Calendar'[Year Month], 'Calendar'[yearMonthNo] ), [Sales] )
RETURN 
SWITCH(
    TRUE(),
    __countDays = __countAllDaysInYear, __rankYear,
    __countDays = __countAllDaysInMonth, __rankMonth
)

 This is the result

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz,

thanks for looking into this.
I really like the Idea of combining COUNTROWS with RANKX-Function.
I'm pretty sure that's the approach how to solve that problem.

I tried something similar, but do you see a way, to start the Rank again in 

Case there was no sale on the hierarchy level?
Lets assume there was no Sale at a certain day, so the flag would be 0 for that day.
If there was a sale the next day, the Flag should start with 1 again, and add up as long as
the series continues until it finally gets to a day without Sale and 0 again.

Again thank you so much for talking the time.
Of course if I'll find the final Solution I'll let you know

Markus