Forum Discussion

anusha_madhavan's avatar
anusha_madhavan
Frequent Visitor
1 year ago
Solved

How to display previous data for null values in column charts?

Sent by you:
I have few measures from a table which are enabled repos, not enabled repos in power bi and I have calender date column as well. Now, I have stacked column chart that displays these values.The measures are in y axis and calender date in x axis and I consider MonthInCalenderShort. For certain months, there is no value for enabled repos and not enabled repos. In such scenarios, I would like to display the count of enabled and not enabled repos of the previous month. How to do that?

 

I have already tried using carryforward and nonblank scenarios.
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, anusha_madhavan 

    Based on your information, I create a sample table:

     

    Then create new measures, try the following DAX:

    LastNonBlankEnabledRepos = 
CALCULATE(
    LASTNONBLANK('Table'[EnabledRepos], CALCULATE(SUM('Table'[EnabledRepos]))),
    FILTER(
        ALL('Table'),
        'Table'[CalendarDate] <= MAX('Table'[CalendarDate])
    )
)
    LastNonBlankNotEnabledRepos = 
CALCULATE(
    LASTNONBLANK('Table'[NotEnabledRepos], CALCULATE(SUM('Table'[NotEnabledRepos]))),
    FILTER(
        ALL('Table'),
        'Table'[CalendarDate] <= MAX('Table'[CalendarDate])
    )
)

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, anusha_madhavan 

    Based on your information, I create a sample table:

     

    Then create new measures, try the following DAX:

    LastNonBlankEnabledRepos = 
CALCULATE(
    LASTNONBLANK('Table'[EnabledRepos], CALCULATE(SUM('Table'[EnabledRepos]))),
    FILTER(
        ALL('Table'),
        'Table'[CalendarDate] <= MAX('Table'[CalendarDate])
    )
)
    LastNonBlankNotEnabledRepos = 
CALCULATE(
    LASTNONBLANK('Table'[NotEnabledRepos], CALCULATE(SUM('Table'[NotEnabledRepos]))),
    FILTER(
        ALL('Table'),
        'Table'[CalendarDate] <= MAX('Table'[CalendarDate])
    )
)

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.