Showing Previous Month /Current Month Values in Future Month values

[Prodosh]

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

[Anonymous]

I have created a new measure and break it down by months like shown below,

month 1=data in jan'2020

month 2=data in feb'2020

month 3=data in mar'2020

month 4=data in apr'2020

month 5=data in may'2020

Now, im having problem to display the previous months values in the current month row like the one shown below, im not sure how to get that results as expected.

month 1=data in dec'19

month 2=data in jan'2020

month3=data in feb'2020

month4=data in mar'2020

month 5=data in apr'2020

month 6=data in may'2020

Is there anyone having any idea about this?
My dashboard has only a year filter.

[Prodosh]

Hi @Anonymous  ,

Can you provide any sample data / screenshot regarding your requirement  . 

Thanks 

Prodosh 

[Anonymous]

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

[Prodosh]

Hi @Anonymous  ,

Here is solution for your Sample Data for calculating previous month with screenshot Below 

Dax Query :  

Previous Month Sales = CALCULATE(SUM(Sheet1[Sales]),PREVIOUSMONTH('Dates'[Date]))

( For Calculating this you need to have the date dimension table in your pbix file .)

Hope this solution will work for you 🙂

Screenshot ,

[Anonymous]

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!:) 

[Prodosh]

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 .

[Anonymous]

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.

[v-jiascu-msft · Accepted 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

[Prodosh]

Hi @v-jiascu-msft

Thanks for your Reply. this logic is working fine.

[Greg_Deckler]

So, are you saying that those last 3 values should show the value for 2018/09 (441,600.11) instead of blank?

[Prodosh]

yes