calculate the last 3 months from the selected date and the blank values that are 0

[Anonymous]

Hello everybody,

I need help. I have a model with a date dimension with INT field related to the fact table. In turn I have a dim period, with weekly and monthly values. This dimension filters the fact table which in turn filters the fact table. In the fact table I have records for weekly values and for monthly values.

I am trying to paint in a graph of lines, the values of the last 6 weeks or 6 months depending if I have filtered the month or the week and its respective date value.

the dax is

 Nro Problemas = 

var weeks = CALCULATE(DISTINCTCOUNT('fact'[Id Problema]),all('dim calendario'), DATESINPERIOD('dim calendario'[fecha],SELECTEDVALUE('dim calendario'[fecha]),-42,DAY),'dim Periodo')

var months= CALCULATE(DISTINCTCOUNT('fact'[Id Problema]),all('dim calendario'), DATESINPERIOD('dim calendario'[fecha],SELECTEDVALUE('dim calendario'[fecha]),-6,MONTH),'dim Periodo')

return

IF(MAX('dim calendario'[id_Periodo]) = 10 --week

,weeks

,IF(MAX('dim calendario'[id_Periodo]) = 11 --month

,months))

I need to paint the blanks as 0. I have tried the IF (measure = blank (), 0, measure) thing. But it paints me 0 on all dates. Whether from period 10 (week) or period 11 (month).

I need and hope to paint is this. Thanks

[dedelman_clng]

Hi @Anonymous -

Try adding +0 to the calculations in your measure

Nro Problemas =
VAR weeks =
    CALCULATE (
        DISTINCTCOUNT ( 'fact'[Id Problema] ),
        ALL ( 'dim calendario' ),
        DATESINPERIOD (
            'dim calendario'[fecha],
            SELECTEDVALUE ( 'dim calendario'[fecha] ),
            -42,
            DAY
        ),
        'dim Periodo'
    ) + 0       ////Adding +0 here
VAR months =
    CALCULATE (
        DISTINCTCOUNT ( 'fact'[Id Problema] ),
        ALL ( 'dim calendario' ),
        DATESINPERIOD (
            'dim calendario'[fecha],
            SELECTEDVALUE ( 'dim calendario'[fecha] ),
            -6,
            MONTH
        ),
        'dim Periodo'
    ) + 0       ////Adding +0 here
RETURN
    IF (
        MAX ( 'dim calendario'[id_Periodo] ) = 10 --week
        ,
        weeks,
        IF ( MAX ( 'dim calendario'[id_Periodo] ) = 11 --month
            , months )
    )

Hope this helps

David

[Anonymous]

Hi dedelman_clng,

Thanks for answering. This I already tried, but the result is the following.

It paints all the dates to 0 ... It is as if when putting the "+0", it did not respect the DATESINPERIOD.

Thanks.