Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
4 years ago
Solved

Null rate in Power BI

Hi,

 

Getting issue. I wanted to count rate comparison this year last year in sales. But getting null values, no idea why it happens?

mok_ly = 
IF( HASONEVALUE('Date'[Metai]) ,CALCULATE ( [moketina_suma],
    DATEADD ('Date'[Date],-1,YEAR ) ) )

[moketina_suma] measure is generated from table called Pardavimai. I made a relationship between Date and Pardavimai but getting:

 

Even I selected  this year and still null values. Why it happens?

 

Here is a picture of relationship:

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Analitika,

    Perhaps you can try to use the date function to manually define the filter range for calculation.

    mok_ly =
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [moketina_suma],
        FILTER (
            ALLSELECTED ( 'Fact' ),
            'Fact'[Date]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        ),
        VALUES ( 'Date'[Metai] )
    )

    Notice: replace the 'fact' table with the table your 'moketina_suma' formula is calculated.
    Regards,

    Xiaoxin Sheng

2 Replies

  • Analitika's avatar
    Analitika
    Post Prodigy
    4 years ago

    Hi,

     

    I would like to ask why it happens that has one value generates blank values. Here is a measure:

    mok_ly = 
IF( HASONEVALUE('Date'[Metai]) ,CALCULATE ( [moketina_suma],
    DATEADD ('Date'[Date],-1,YEAR ) ) )

     

    Here is a blank third column which uses measure above.

     

    Here is a relationship between Pardavimai and Date tables.

     

    So how to solve my issue?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Analitika,

    Perhaps you can try to use the date function to manually define the filter range for calculation.

    mok_ly =
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [moketina_suma],
        FILTER (
            ALLSELECTED ( 'Fact' ),
            'Fact'[Date]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        ),
        VALUES ( 'Date'[Metai] )
    )

    Notice: replace the 'fact' table with the table your 'moketina_suma' formula is calculated.
    Regards,

    Xiaoxin Sheng