Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an issue where I would like for a month to show the 67% of the company's value for 2 months ago plus 33% of the company's value for the previous month as I would like to try to compare the values to the current month's sales (could be in 2 different tables ). I added a column (Next month) that would project the next month out. I wrote the following code that I thought would accomplish this but I am getting odd results
Pushed Amount = var amount = Sheet2[Sale Amount] var month1 = Sheet2[Next Date] var month2 = PREVIOUSMONTH(Sheet2[Next Date]) var dealdate =Sheet2[Deal Date] var saleamount = Sheet2[Sale Amount] var company = Sheet2[Company] var pushamount = iferror(CALCULATE(SUM(Sheet2[Sale Amount]), Sheet2[Deal Date] = month2,Sheet2[Company] = company),0)*.67 + iferror(CALCULATE(SUM(Sheet2[Sale Amount]), Sheet2[Deal Date] = month1, Sheet2[Company] = company),0)*.33 return pushamount
These are the results that I am getting:
Should produce the same values as in the table below:
Solved! Go to Solution.
Solved this by creating a date table and a mesaure, code for the measure below
Measure = var month1 = CALCULATE(SUM(Sheet2[Sale Amount]),DATEADD('date'[Date],-1,MONTH)) var month2 = CALCULATE(SUM(Sheet2[Sale Amount]),DATEADD('date'[Date],-2,MONTH)) return .33*month1+.67*month2
One thing that tripped me up was using the incorrect date column when creating visualizations. I was using the one in my original table when I should have been using the one from the date table. If you are getting the exact same number in the same month then this is likely why
Hi,
Why do you want that result as a calculated column - why not a measure? Share the link from where i can download the PBI file.
I'm open to using a measure instead, I'm not sure when to use one instead of another
Solved this by creating a date table and a mesaure, code for the measure below
Measure = var month1 = CALCULATE(SUM(Sheet2[Sale Amount]),DATEADD('date'[Date],-1,MONTH)) var month2 = CALCULATE(SUM(Sheet2[Sale Amount]),DATEADD('date'[Date],-2,MONTH)) return .33*month1+.67*month2
One thing that tripped me up was using the incorrect date column when creating visualizations. I was using the one in my original table when I should have been using the one from the date table. If you are getting the exact same number in the same month then this is likely why
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |