Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
9 years ago
Solved

Previous Total

Hi all,   I'm trying to calculate a figure based on a previous total, I need to decrement two columns in order to calculate but having a few issues.   My data and required output looks like   ...
Need Help
  • itchyeyeballs's avatar
    itchyeyeballs
    9 years ago

    Hi tringuyenminh92

     

    Thank you for the help, I wasn't able to follow your complete solution but used some of the core parts to resolve.

     

    As you suggested I merged the two columns I wanted to filter on into a single column, I did this in the initial query

     

    = Table.AddColumn(#"Renamed Columns", "Year_level", each [Year_]*100+[level])

     

    I then created a function to dynamically work out the value (this included an extra filter in the calculate)

    =VAR
	yearx = max(table[level])
RETURN
	Calculate(
		sum(table[value]),
		filter(all(table[level],table[Year]),table[year_level]= yearx-101),
		table[another_col]="END"
	)

    I then created another measure to ensure the overall total was calulcated correctly

     

    =if(HASONEVALUE(table[Year]),
[Measure],
sumx(values(Table[Year]),[Measure])
)

    Which seems to work.

     

    Thank you for the help, was invaluable

     

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi itchyeyeballs,

 

I have an idea with trick based on time-pattern as below:

  • Create calculated column to have unique column from Year and Level 

 

Level&Year = RawData[Level] * 10000 + RawData[Year]

 

  • Create Calcualted Table for Level&Year column with distinct values 
Lv&Y = DISTINCT(RawData[Level&Year])
Level = DIVIDE( 'Lv&Y'[Level&Year],10000)
year = MOD('Lv&Y'[Level&Year],10000)

(i extract level and year columns for display purpose only)

 

  • Create calculated measure for Previous Total:
prev = CALCULATE(SUM(RawData[Value]),FILTER(all('Lv&Y'),'Lv&Y'[Level&Year] = MAX('Lv&Y'[prev - lv&y])  ))

 

Please check my sample data and sample pbix file for more details. Hope this works for your case.

I'd like to know the formula for prev of year=2014 and level=1

 

 

 

  • itchyeyeballs's avatar
    itchyeyeballs
    Impactful Individual
    9 years ago

    Hi tringuyenminh92

     

    Thank you for the help, I wasn't able to follow your complete solution but used some of the core parts to resolve.

     

    As you suggested I merged the two columns I wanted to filter on into a single column, I did this in the initial query

     

    = Table.AddColumn(#"Renamed Columns", "Year_level", each [Year_]*100+[level])

     

    I then created a function to dynamically work out the value (this included an extra filter in the calculate)

    =VAR
	yearx = max(table[level])
RETURN
	Calculate(
		sum(table[value]),
		filter(all(table[level],table[Year]),table[year_level]= yearx-101),
		table[another_col]="END"
	)

    I then created another measure to ensure the overall total was calulcated correctly

     

    =if(HASONEVALUE(table[Year]),
[Measure],
sumx(values(Table[Year]),[Measure])
)

    Which seems to work.

     

    Thank you for the help, was invaluable

     

    • tringuyenminh92's avatar
      tringuyenminh92
      Memorable Member
      9 years ago

      Hi itchyeyeballs,

       

      In Vietnamese, invaluable means Vô Giá = Non-valuable =)) just kidding, it's good to know that there is solution for your case.