Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 previous month. This is an example where I have the columns Date and Value in my table, but I need to create the column 'Difference'.
I have also tried this DAX-formula, but the value only shows up in one date so it doesn't work.
sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]);DATEADD(DW_Data_Salg[DATO].[Date];-1;MONTH))
Is there someone who know how to do this?
Date | Value | Difference |
15.02.2016 | 50 000 | 50 000 |
15.03.2016 | 70 000 | 20 000 |
15.04.2016 | 70 000 | 0 |
15.05.2016 | 80 000 | 10 000 |
15.06.2016 | 20 000 | -60 000 |
Best regards,
Tormod (Norway)
Solved! Go to Solution.
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,
Hi, could anyone please guide me as to how to get %difference of previous month ? I am getting blanks for months not required in the report , have to hide these blank columns , very taxing .
I am using the following DAX impression -
Need help in fixing this, kindly suggest
I have tried DAX impression to calcualte %difference over previous month, however the result gives me blank value for the months not required , ex, in screenshot I am also getting blank values for Dec over Nov which is not required , also it shows me Feb blank values as well. I only need the %diff for Dec & Jan . Formula used -
Hi Please see image below which I created through Quick Measure & Time Intelligence:
So my dataset is from Sep 30, 2020 to Feb 28, 2021. I calculated the %diff MoM, How can I hide March row since we do not have March data as of now and how to remove the Total column from waterfall chart created by this table?
Also is there a way to reflect the Total column as a % change of difference from Sep 30 2020 and Feb 28 2021 data? Whether there has been overall decrease or increase in Feb 2021 from starting point Sep 2020? How to have this formula as a separate measure in itself which keeps on updating when March and future months are added with base point being Sept 30, 2020?
@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 there @ankitpatira
Your DAX code finally lead me to the solution I needed, thanks a lot, however, may I ask why the conditional before looking up the value?
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
Hi and thanx for your solution, but there is one problem. My table consists of 2 mill rows and 40 columns and the reference is department. So the lookup is on department and for the previous month.
Index | Date | Department | Value | Difference |
100 | 15.02.2016 | 100 | 50 000 | 50 000 |
2345 | 15.03.2016 | 100 | 70 000 | 20 000 |
7585 | 15.04.2016 | 100 | 70 000 | 0 |
654325 | 15.05.2016 | 100 | 80 000 | 10 000 |
345321 | 15.06.2016 | 100 | 20 000 | -60 000 |
Hi @Tormod_GK
I encountered almost the same problem as you, my solution was to use a calculated index based on the lookup values I needed and using the concatenate function; you would first need to generate the index as a calculated column, as follows:
ConcatIndex = TABLENAME[Index] & TABLENAME[Date] & TABLENAME[Department]
Then, use the ConcatIndex to retrieve the value for each Department & Index and the PREVIOUSMONTH function for the previous month:
Difference =
TABLENAME[Value] - IF(
TABLENAME[Value] = 0,
TABLENAME[Value],
LOOKUPVALUE(
TABLENAME[Value],
TABLENAME[ConcatIndex],
(TABLENAME[Index] & (PREVIOUSMONTH(TABLENAME[Date] & TABLENAME[Department])))
)
I'm not quite sure what's the purpose of the conditional on the last code, it's up to you to use it or not.
I hope this helps to you or someone else, even though i'm 4 years late hehe.
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,
I have data from GitHub on the COVID cases and deaths. It has daily counts for each county in the US, for every day since January 22. I would like to be able to calculate the difference between days ("new cases" or "new deaths"). I have been able to do that in Tableau, but have not figured out how to make it happen in a calculated column in M or Dax in Power BI. In other words, for a particular County in a particular State (or for a particular FIPS Code), I need to be able to subtract the count of Confirmed Cases for Jan 22 from the same count for Jan 23. Also, if the result ends up less than zero, I need to be able to have it return 0. Any ideas? I could build a concatenated index for county, state, plus an index number, if that helps.
What is "DATO" in this expression?
DATO = DATE 🙂
I have a similar problem, try this solution but it shows me the following:
Using this formula always results in zero for me MOV_NETO_RRC
I would like to filter from January to April and only show me the values enclosed with blue.
My applied measure is as follows:
I have been trying to carry out a similar calculation, but whenever I use a DAX function, such as PREVIOUSYEAR, as a filter in CALCULATE nothing gets returned.
My current expression is:
Difference = CALCULATE(SUM('Yearly Summary'[Sales by Status per Year]), PREVIOUSYEAR('Yearly Summary'[Date].[Date]))
But this returns just blanks. Any idea why this may be or ideas for trouble shooting? If I drop the filter, it works. If I try other filters that are not DAX functions such as 'Yearly Summary'[Date].[Date]=2017 it works.
I aslo got blank results after implementing PREVIOUSYEAR function same as you. However problem resolved when
1) I added Date table with all uninterrupted dates within specified period.
2) Marked it as date table with date column as key.
3) Established relation between date table and data table.
4) Use date table column in PREVIOUSYEAR function in stead of date column from data table.
It worked as expected after following steps above.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
98 | |
41 | |
38 |
User | Count |
---|---|
152 | |
123 | |
80 | |
73 | |
73 |