Forum Discussion

bencefarsang's avatar
bencefarsang
New Member
5 years ago
Solved

How to remove empty rows with missing data from a calculated table chart?

I've got the following table chart about sales data. The third and fourth columns are calculated by time intelligence (month-over-month and year-over-year changes). I haven't got any data from 2020M1...
Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    bencefarsang 

    Just follow the pattern shown earlier: 

     

    Sales MoM% V2 =
VAR result_ =
    IF (
        ISFILTERED ( 'Table1'[DATE] );
        ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." );
        VAR __PREV_MONTH =
            CALCULATE (
                SUM ( 'Table1'[Sales] );
                DATEADD ( 'Table1'[DATE].[Date]; -1; MONTH )
            )
        RETURN
            DIVIDE ( SUM ( 'Table1'[Sales] ) - __PREV_MONTH; __PREV_MONTH )
    )
RETURN
    IF ( result_ = -1; result_ )

     

    or if you want to do the check on Sales (using your current [Sales MoM%] measure

    Sales MoM% V2 =
VAR result_ = [Sales]
RETURN
    IF ( NOT ISBLANK(result_); [Sales MoM%] )

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

AlB's avatar
AlB
Community Champion
5 years ago

bencefarsang 

Just follow the pattern shown earlier: 

 

Sales MoM% V2 =
VAR result_ =
    IF (
        ISFILTERED ( 'Table1'[DATE] );
        ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." );
        VAR __PREV_MONTH =
            CALCULATE (
                SUM ( 'Table1'[Sales] );
                DATEADD ( 'Table1'[DATE].[Date]; -1; MONTH )
            )
        RETURN
            DIVIDE ( SUM ( 'Table1'[Sales] ) - __PREV_MONTH; __PREV_MONTH )
    )
RETURN
    IF ( result_ = -1; result_ )

 

or if you want to do the check on Sales (using your current [Sales MoM%] measure

Sales MoM% V2 =
VAR result_ = [Sales]
RETURN
    IF ( NOT ISBLANK(result_); [Sales MoM%] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers