Cohort write 0 if no sales yet

[Anonymous]

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 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 

[kentyler]

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

[Anonymous]

@kentyler do you mean

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

? 

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

[az38]

@Anonymous 

whats your desired result? could you provide an example?

[Anonymous]

@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
    )

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

[Anonymous · 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