Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
I have a requirement in which i need to update the Current Month value to Upcoming Month values that are showing null.For Example in the Below Scrrenshot the Sales value in Current month 2018/09 should get updated to 2018/10, 2018/11, 2018/12
Please find the Screenshot Below. Any Help would be appreciated. 🙂
Solved! Go to Solution.
Hi @Prodosh,
We could use a measure like below. If you need the exact formula, please provide a dummy sample.
Measure 7 = VAR lastDateHasSales = CALCULATE ( LASTNONBLANK ( 'Calendar'[Date], CALCULATE ( SUM ( FactSales[SalesQuantity] ) ) ), ALL ( 'Calendar' ) ) RETURN IF ( ISBLANK ( SUM ( FactSales[SalesQuantity] ) ), CALCULATE ( SUM ( FactSales[SalesQuantity] ), FILTER ( ALL ( 'Calendar' ), YEAR ( 'Calendar'[Date] ) = YEAR ( lastDateHasSales ) && MONTH ( 'Calendar'[Date] ) = MONTH ( lastDateHasSales ) ) ), SUM ( FactSales[SalesQuantity] ) )
Best Regards,
Dale
I have created a new measure and break it down by months like shown below,
Hi @Anonymous ,
Can you provide any sample data / screenshot regarding your requirement .
Thanks
Prodosh
sure here it is, i cannot use the previous month function because my values are in measures and it has filter in it and i have to use the Calculate function.
Current data | Expected data | |||||
month | measure | month | measure | |||
2019 | 1 | -9712105329.827301 | 2020 | 1 | 216477848.1 | |
2019 | 2 | 8734366.182 | 2020 | 2 | '-15347451899.259367 | |
2019 | 3 | 79800885.36 | 2020 | 3 | '-24375508.06959456 | |
2019 | 4 | '-6813238643.463575 | 2020 | 4 | 23003857.52 | |
2019 | 5 | '-199365784.79792812 | 2020 | 5 | '-79316170.69565716 | |
2019 | 6 | '-1917799765.134318 | 2020 | 6 | 26844790.12 | |
2019 | 7 | 60077560.55 | 2020 | 7 | 0 | |
2019 | 8 | '-116559690.36106066 | 2020 | 8 | 367394211.5 | |
2019 | 9 | '-15173986.473717645 | 2020 | 9 | 0 | |
2019 | 10 | '-6003066388.408958 | 2020 | 10 | 0 | |
2019 | 11 | 49650715.74 | 2020 | 11 | 0 | |
2019 | 12 | 216477848.1 | 2020 | 12 | 0 | |
2020 | 1 | '-15347451899.259367 | ||||
2020 | 2 | '-24375508.06959456 | ||||
2020 | 3 | 23003857.52 | ||||
2020 | 4 | '-79316170.69565716 | ||||
2020 | 5 | 26844790.12 | ||||
2020 | 6 | 0 | ||||
2020 | 7 | 367394211.5 | ||||
2020 | 8 | 0 | ||||
2020 | 9 | 0 | ||||
2020 | 10 | 0 | ||||
2020 | 11 | 0 | ||||
2020 | 12 | 0 |
Hi @Anonymous ,
Here is solution for your Sample Data for calculating previous month with screenshot Below
Dax Query :
Hope this solution will work for you 🙂
Screenshot ,
Hi,
Thanks for you reply but im afraid it did not work as the sales values are actually outcome of a measure, not a direct dataset. Im trying to get the previous measure values. Since, its a measure, i cant use the calculate function right. Let me know if you have any idea on it but thanks again!:)
Hi @Anonymous ,
It doesn't matter for Sales value calculated which is outcome of measures as you mentioned . As long as there is proper modelling and date dimension is working fine . the Previous month function will also work fine .
Usually calculate function always works for measures 🙂
By the way , which data source are you using for report .
Hi,
Thanks for the input. By measures i meant a newly created measure inside power bi. This measure has a calculation and filtered with few dimension. Hence, im not able to use the previousmonth function because it keeps giving me back the current month values.
Hi @Prodosh,
We could use a measure like below. If you need the exact formula, please provide a dummy sample.
Measure 7 = VAR lastDateHasSales = CALCULATE ( LASTNONBLANK ( 'Calendar'[Date], CALCULATE ( SUM ( FactSales[SalesQuantity] ) ) ), ALL ( 'Calendar' ) ) RETURN IF ( ISBLANK ( SUM ( FactSales[SalesQuantity] ) ), CALCULATE ( SUM ( FactSales[SalesQuantity] ), FILTER ( ALL ( 'Calendar' ), YEAR ( 'Calendar'[Date] ) = YEAR ( lastDateHasSales ) && MONTH ( 'Calendar'[Date] ) = MONTH ( lastDateHasSales ) ) ), SUM ( FactSales[SalesQuantity] ) )
Best Regards,
Dale
So, are you saying that those last 3 values should show the value for 2018/09 (441,600.11) instead of blank?
yes
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |