Forum Discussion

JoaoMS's avatar
JoaoMS
Icon for Helper III rankHelper III
6 years ago
Solved

Cumulative chart with empty data in some months

Hi all, I have the following visualizations regarding cumulative Purchase Orders:

Due to there are no data for June in 2020, I need that the cumulative of June should be completed with the cumulative of the previous month. I'm using the following measure for "2020":

 

2020 = if(isblank([Monto YTD]),blank(),CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))

 

whrere:

* Monto YTD = SUM(PurchaseOrder[MontoUSD])

* Calendario = Calendar Table

 

Thanks in advance.

Joao

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    JoaoMS , Try like

    2020 = if(isblank([Monto YTD]) && max(Calendario[Date]) >today(),blank() ,CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    JoaoMS - Sorry, what is the question? 

     

    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    JoaoMS , then why are using isblank, remove that and try

    2020 = CALCULATE([Monto YTD] , DATESYTD(Calendario[Date]))

    This will give data in June

    • JoaoMS's avatar
      JoaoMS
      Icon for Helper III rankHelper III
      6 years ago
      Hi, thank you for the quick response. Indeed, it works for June, but now, the following months (september to december) have the cumulative of August, and I want those months to be "blank". Is there a way to consider those months empty? Regards, Joao
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        for that put a filter like this and try

        My data example

        YTD QTY forced=
        var _max = today()
        return
        calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
        //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))

         

        or

         

        YTD QTY forced=
        var _max = maxx('order',[Order date])
        return
        calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
        //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))

         

        Commented is alternate option

        Ideally, we should relative/advance filter , less than today

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    Power bi rolled over per month with blank months

    Good afternoon

    Please, could you help me, I have companies with data up to the month of October ( 10) , when segment in month 10 , only Development appears. How do I make culture appear with its last accumulated of June (6)