Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

table visual contain 2 separate year values sum together

Create a report using table visual in power bi...Here .sum of budget is calculated for year 2022 ..but sum of actual is calcualte based on sum of actual for the year 2021...

I used measure for calcualting 2nd and 3rd colum,,,But am faccing an issue while using value column for the table as first column it is not working properly as i expected...calculation is becoming wrong...Is it possoble to have a table contain first column as monthname with 2022 year values and budget column is also based on 2022 yr value but the column Actual having 2021 yr value some in one table?Plse help

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    What is the data type of [value] column? Text or date?

    Assume it's text, please refer this formula.

    sum of Actual =
CALCULATE (
    SUM ( 'table'[surcharge] ),
    FILTER (
        ALL ( 'table' ),
        FORMAT ( EDATE ( 'table'[date], 12 ), "MM/YYYY" )
            = SELECTEDVALUE ( 'calendar'[value] )
    )
)

    If I misunderstood your meaning, please share some sample data and expected result.

     

    Best Regards,

    Jay

3 Replies

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous 

    Not sure about the dataset in this case but can you share the DAX Code used for the two measures ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      sum of budget = CALCULATE(SUM('table'[gross_surcharge]),FILTER('Calendar','Calendar'[YEAR]="2022"))
      sum of Actual= CALCULATE(SUM('table'[surcharge]),FILTER('Calendar','Calendar'[YEAR]="2021"))
      While month name using here calcualtion is wrong..because month name having values only for the year 2022...
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    What is the data type of [value] column? Text or date?

    Assume it's text, please refer this formula.

    sum of Actual =
CALCULATE (
    SUM ( 'table'[surcharge] ),
    FILTER (
        ALL ( 'table' ),
        FORMAT ( EDATE ( 'table'[date], 12 ), "MM/YYYY" )
            = SELECTEDVALUE ( 'calendar'[value] )
    )
)

    If I misunderstood your meaning, please share some sample data and expected result.

     

    Best Regards,

    Jay