Forum Discussion
Previous Month value
Hello guys,
I really hope someone can help me figuring this out.
My Question:
Since I have no data for september yet I am trying to fill it with the value before, but I can't figure out how.
Same thing should also happen to october. So baisically both should be 600.
If there is any data coming in for september, 600 should be relplaced by the actual value.
EDIT
Informations you might need:
Since I can't show you the actual data I created random tables to give you guys more detail:
This is what my issue looks like:
September and october are both empty since there is no data:
Hi Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a date dimension table
Date = CALENDAR(date(2022,1,1),TODAY())2. Create a measure as below
Measure = VAR _selmonth = SELECTEDVALUE ( 'Date'[Date].[MonthNo] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Data] ), ALLSELECTED ( 'Table' ) ) VAR _month = MONTH ( _maxdate ) VAR _lastvalue = CALCULATE ( SUM ( 'Table'[value] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Data] ) = YEAR ( _maxdate ) && MONTH ( 'Table'[Data] ) = _month ) ) RETURN IF ( _selmonth <= _month, SUM ( 'Table'[value] ), _lastvalue )If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
10 Replies
Hi Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a date dimension table
Date = CALENDAR(date(2022,1,1),TODAY())2. Create a measure as below
Measure = VAR _selmonth = SELECTEDVALUE ( 'Date'[Date].[MonthNo] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Data] ), ALLSELECTED ( 'Table' ) ) VAR _month = MONTH ( _maxdate ) VAR _lastvalue = CALCULATE ( SUM ( 'Table'[value] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Data] ) = YEAR ( _maxdate ) && MONTH ( 'Table'[Data] ) = _month ) ) RETURN IF ( _selmonth <= _month, SUM ( 'Table'[value] ), _lastvalue )If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Anonymous Hi this can be done in power query through the fill option.
Go to power query==>right-click the value column==>Click on Fill ==> Down.Below is the output. Hope this helps.
Anonymous fill option did not work? or did you get any errors? Please share some more information it will help me better
Hi Anonymous
You can write a DAX and calculate a calculated column that says if the value is blank then take previous month value.
= IF(ISBLANK(value), CALCULATE(SUM(value), PREVIOUSMONTH(Date)), SUM(value))
For this please refer the document for PREVIOUSMONTH DAX formula.
Hope this helps!
Thank you
