Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX/M Query

Hi Team,

 

Good Morning!

 

I need Sumif calculation for YDT process insert into summary table, see the below screenshot, 

 

Summary Table- need to add "YTD-20" column for few months sum calculation.

 

 

Thanks,

KVB

  • Hi, Anonymous 

     

    Based on your description, I create data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

    Result = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=MAX('Table'[Date])&&
            'Table'[GHT] = SELECTEDVALUE('Table'[GHT])
        )
    )

     

    Then you may use a matrix visual to diplay the result.

     

    Best Regards

    Allan

     

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

3 Replies

  • Anonymous , not very clear, but you can get YTD wit date table

     

    YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
    LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
    Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
    
    
    YTD QTY forced= 
    var _max = today()
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
    
    LYTD QTY forced= 
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
    
    
    YTD QTY forced= 
    var _max = maxx('order',[Order date])
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
    
    LYTD QTY forced= 
    var _max1 =maxx('order',[Order date])
    var _max = date(year(_max1)-1,month(_max1),day(_max1))
    return
    CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
    

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak 

       

      Thanks for your help,

       

      I need (Feb to Nov month) sum of value.

       

      Thanks,

      KVB

  • v-alq-msft's avatar
    v-alq-msft
    Community Support

    Hi, Anonymous 

     

    Based on your description, I create data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

    Result = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=MAX('Table'[Date])&&
            'Table'[GHT] = SELECTEDVALUE('Table'[GHT])
        )
    )

     

    Then you may use a matrix visual to diplay the result.

     

    Best Regards

    Allan

     

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