cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vk_pro
Helper I
Helper I

Cohort write 0 if no sales yet

Hello everyone! I need help with a DAX measure. Maybe you can give me a few tips about how to resolve these issues:
1. I want to fill numbers to cells that can have numbers. Set 0 numbers in the yellow field of the cohort chart. (image example for the same dataset below)
2. Restrict X-axis (If MonthAfter in Fact table is empty I should hide it)

write 0write 0

DAX measure:

Solve perc = 
VAR total_cases = CALCULATE( COUNT( FactSales[Id] ), ALL( DimMonthsAfter[MonthCount] ) )
VAR cases_by_period =
    CALCULATE(
        COUNT( FactSales[Id] ),
        FILTER( FactSales, FactSales[DateFinished] <> BLANK() )
    )
VAR solve_perc = DIVIDE( cases_by_period, total_cases, BLANK() )
VAR lastDateWithValue =
    CALCULATE(
        MAX( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR firstDateWithValue =
    CALCULATE(
        MIN( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
RETURN
    IF(
        MIN( 'DimDates'[Date] ) <= EDATE(firstDateWithValue, -1) || MAX( 'DimDates'[Date] ) >= EDATE(lastDateWithValue, 1),
        BLANK(),
        solve_perc + 0
    )

pbix file 

1 ACCEPTED SOLUTION

Thanks all for the reply! But I did for myself. I reorganized Dax measure:

Solve perc = 
VAR total_cases = 
    CALCULATE( COUNT( FactSales[Id] ), ALL( DimMonthsAfter[MonthCount] ) )
VAR cases_by_period =
    CALCULATE(
        COUNT( FactSales[Id] ),
        FILTER( FactSales, FactSales[DateFinished] <> BLANK() )
    )
VAR solve_perc = 
    DIVIDE( cases_by_period, total_cases, BLANK() )
VAR lastDateWithValue =
    CALCULATE(
        MAX( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR firstDateWithValue =
    CALCULATE(
        MIN( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR maxCountAfter =
    CALCULATE (
        MAX ( 'FactSales'[MonthsCountAfterInvoice] ),
        ALLSELECTED ( 'FactSales' )
    )
VAR countMonthFromSalesToNow = 
    DATEDIFF(MIN(DimDates[Date]), TODAY(), MONTH)
RETURN
    IF(
        (ISFILTERED(DimDates[MonthYearNum]) = FALSE() || (MIN( 'DimDates'[Date] ) > EDATE(firstDateWithValue, -1) && MAX( 'DimDates'[Date] ) <= EDATE(lastDateWithValue, 1)))
        && (ISFILTERED(DimMonthsAfter[MonthCount]) = FALSE() || MAX(DimMonthsAfter[MonthCount]) <= countMonthFromSalesToNow), 
        solve_perc + 0,
        BLANK()
    )

 

So you can see here maxCountAfter variable, which helps me restrict Y-axis. Also, I did a Total return with IsFiltered function. Maybe it will helpful for others. The result : here

View solution in original post

5 REPLIES 5
kentyler
Solution Sage
Solution Sage

Have you tried returning a 0 instead of BLANK() in the last IF statement ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


@kentyler do you mean

RETURN
    IF(
        MIN( 'DimDates'[Date] ) <= EDATE(firstDateWithValue, -1) || MAX( 'DimDates'[Date] ) >= EDATE(lastDateWithValue, 1),
        0,
        solve_perc + 0
    )

vk_pro_0-1594552481996.png

 

Unfortunately, It doesn't help.  And my Y-axis restriction was broken

@vk_pro 

whats your desired result? could you provide an example?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thanks for your reply.

Duplicate the link with pbix file here  

We have a cohort chart.

At the image in my question, you can see the triangle (yellow part) that I want to fill with numbers. If a measure has value I should show you. If hasn't it should be 0. But only in cells that can have values. (so I want to fill empty cells that can have values)

I suppose that I should restrict values by Y-AXIS (maxCountAfter) something like that:

 

Solve perc = 
VAR total_cases = CALCULATE( COUNT( FactSales[Id] ), ALL( DimMonthsAfter[MonthCount] ) )
VAR cases_by_period =
    CALCULATE(
        COUNT( FactSales[Id] ),
        FILTER( FactSales, FactSales[DateFinished] <> BLANK() )
    )
VAR solve_perc = DIVIDE( cases_by_period, total_cases, BLANK() )
VAR lastDateWithValue =
    CALCULATE(
        MAX( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR firstDateWithValue =
    CALCULATE(
        MIN( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR maxCountAfter =
    CALCULATE (
        MAX ( 'FactSales'[MonthsCountAfterInvoice] ),
        ALLSELECTED ( 'FactSales' )
    )
RETURN
    IF(
        MIN( 'DimDates'[Date] ) <= EDATE(firstDateWithValue, -1) || MAX( 'DimDates'[Date] ) >= EDATE(lastDateWithValue, 1) ||
        MAX(DimMonthsAfter[MonthCount]) >= maxCountAfter + 1,
        BLANK(),
        solve_perc + 0
    )

 

vk_pro_0-1594614533408.png

 

but I don't know how to remove 0,00 values from the right side of the triangle

 

Thanks all for the reply! But I did for myself. I reorganized Dax measure:

Solve perc = 
VAR total_cases = 
    CALCULATE( COUNT( FactSales[Id] ), ALL( DimMonthsAfter[MonthCount] ) )
VAR cases_by_period =
    CALCULATE(
        COUNT( FactSales[Id] ),
        FILTER( FactSales, FactSales[DateFinished] <> BLANK() )
    )
VAR solve_perc = 
    DIVIDE( cases_by_period, total_cases, BLANK() )
VAR lastDateWithValue =
    CALCULATE(
        MAX( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR firstDateWithValue =
    CALCULATE(
        MIN( 'FactSales'[DateKey] ),
        FILTER( ALLSELECTED( 'FactSales' ), [DateFinished] <> BLANK() )
    )
VAR maxCountAfter =
    CALCULATE (
        MAX ( 'FactSales'[MonthsCountAfterInvoice] ),
        ALLSELECTED ( 'FactSales' )
    )
VAR countMonthFromSalesToNow = 
    DATEDIFF(MIN(DimDates[Date]), TODAY(), MONTH)
RETURN
    IF(
        (ISFILTERED(DimDates[MonthYearNum]) = FALSE() || (MIN( 'DimDates'[Date] ) > EDATE(firstDateWithValue, -1) && MAX( 'DimDates'[Date] ) <= EDATE(lastDateWithValue, 1)))
        && (ISFILTERED(DimMonthsAfter[MonthCount]) = FALSE() || MAX(DimMonthsAfter[MonthCount]) <= countMonthFromSalesToNow), 
        solve_perc + 0,
        BLANK()
    )

 

So you can see here maxCountAfter variable, which helps me restrict Y-axis. Also, I did a Total return with IsFiltered function. Maybe it will helpful for others. The result : here

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors