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

 

 

I have tried the following but cant get MeasureX to calculate correctly. MeasureX just needs to show the total for the previous year and previous level i.e. 2015 level 2 = 2014 level 1. Both coumns are whole numbers.

 

The forula I have tried are:

=calculate(sum(table([value]),table[Year]-1,table[level]-1)
=calculate(
	sum(table[value]),
	filter(all(table[Year]),table[Year]-1),
	filter(all(table[Level),table[level]-1)
	)

Any ideas? I thought this would be straight forward :(

 

Thanks

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

     

3 Replies

  • 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.