Forum Discussion

Lund's avatar
Lund
Frequent Visitor
3 years ago

Dynamic table SUM next 6 months

Hi I have my table like example:   Date Qty 2022-11-01 22 2022-12-15 36 2023-01-24 1 2023-03-15 4 [...] [...] 2024-01-21 4 2024-01-23 5   What i need, is a dyn...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Lund , New column in the date table and measure you need

 


New column = if(eomonth([Date], 0) < eomonth(Today(),0) , "All Month Before actual", format([Date], "mmm-yyyy"))

 

 

 

measure = calculate(sum(Table[Value]), filter(Table, Table[Date] <= eomonth(today(),6)))

  • Lund's avatar
    Lund
    Frequent Visitor
    3 years ago

    Yeah, not a bad soluiton. That works. ğŸ˜¼
    But i also need a sum of all after next 6 month.

    And the sorting of my column are alphabetich and not by date

     

    My result:

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Lund ,

      You can create a measure as below to get the next 6 months values:

      Measure =
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'table'[date] >= TODAY ()
            && 'table'[date] <= EOMONTH ( TODAY (), 6 )
            && 'table'[date] <= SELECTEDVALUE ( 'date'[date] )
    )
)

      And which field you are using on the matrix Columns field option? What's the data ype of that field? Is it a Text type? You can refer the following blog to custom sort the field values...

      Sort a Column with a Custom Order in Power BI

       

      If the above one can't help you get the desired result, please provide some sample data in your table 'employ' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

      How to upload PBI in Community

      Best Regards