Forum Discussion
Solved
Calculate difference from previous month
Hi. I have a table where I need to subtract two values which are from different rows. I want the difference between the value for the current date in the row and from the row which has the previo...
In this scenario, since you need to get the previous month data based on current slicing date, it's better to create a measure instead of a calculated column. Otherwise, you have to lookup previous row based on index column as ankitpatira suggested. Just create a measure like:
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PARALLELPERIOD(DW_Data_Salg[DATO].[Date],-1,MONTH))
Or
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PREVIOUSMONTH(DW_Data_Salg[DATO].[Date]))
Regards,
Tormod_GK First goto query editor in power bi desktop and under Add column, add index column from zero. Then under modelling tab create new column using below code.
Difference = TABLENAME[Value] - IF( TABLENAME[Value] = 0, TABLENAME[Value], LOOKUPVALUE( TABLENAME[Value], TABLENAME[Index], TABLENAME[Index]-1) )
Hi,
I have table with column names Commodity, Months and Quantity. I want to calculate the difference between month's quantity w.r t. commodity column.
can anyone help me to write DAX query.
| commodity | months | quantity |
| 11 | 4/1/2017 | 500 |
| 11 | 5/1/2017 | 700 |
| 11 | 6/1/2017 | 1000 |
| 11 | 7/1/2017 | 1500 |
| 12 | 4/1/2017 | 600 |
| 12 | 5/1/2017 | 900 |
| 12 | 6/1/2017 | 1400 |
| 12 | 7/1/2017 | 2000 |
| 13 | 4/1/2017 | 100 |
| 13 | 5/1/2017 | 500 |
| 13 | 6/1/2017 | 600 |
| 13 | 7/1/2017 | 750 |
| 14 | 4/1/2017 | 1000 |
| 14 | 5/1/2017 | 2000 |
| 14 | 6/1/2017 | 3000 |
| 14 | 7/1/2017 | 4000 |
Expected result is below:
| commodity | months | quantity |
| 11 | 4/1/2017 | 500 |
| 11 | 5/1/2017 | 200 |
| 11 | 6/1/2017 | 300 |
| 11 | 7/1/2017 | 500 |
| 12 | 4/1/2017 | 600 |
| 12 | 5/1/2017 | 300 |
| 12 | 6/1/2017 | 500 |
| 12 | 7/1/2017 | 600 |
| 13 | 4/1/2017 | 100 |
| 13 | 5/1/2017 | 400 |
| 13 | 6/1/2017 | 100 |
| 13 | 7/1/2017 | 150 |
| 14 | 4/1/2017 | 1000 |
| 14 | 5/1/2017 | 1000 |
| 14 | 6/1/2017 | 1000 |
| 14 | 7/1/2017 | 1000 |
Thanks in advance
Regards,
Manish Nigam