Previous Month value

[anoonymous]

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: 

[v-yiruan-msft · Accepted solution]

Hi @anoonymous ,

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

[anoonymous]

Maybe there is a chance you can tell me how I can use the value for a running total..

[anoonymous]

Thank you 🙂 It worked perfectly 🙂 

[singhsudha1301]

Hi @anoonymous 

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

[anoonymous]

Thank you for your reply. Sadly that did not work:

[AbbasG]

@anoonymous 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.

[anoonymous]

Thank you for your quick answer. But that did not work. I added some further information maybe you can take a look? 

[AbbasG]

@anoonymous fill option did not work? or did you get any errors? Please share some more information it will help me better

[AbbasG]

@anoonymous Also when I replace the data in the source file, it also updates the corresponding month with its actual value.

[anoonymous]

Hello 🙂 It's not working because the data you are using is not the the actual data. I edited my post. March -> September is the visual table. I added an datatable to show you what my actual data looks like. By following your steps nothing happend at all. The visual is still empty 😞