cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Prodosh Frequent Visitor
Frequent Visitor

Showing Previous Month /Current Month Values in Future Month values

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

 

 

 

monthsales.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: Showing Previous Month /Current Month Values in Future 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] )
    )

showing_previous_month

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Showing Previous Month /Current Month Values in Future Month values

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Prodosh Frequent Visitor
Frequent Visitor

Re: Showing Previous Month /Current Month Values in Future Month values

yes

Microsoft v-jiascu-msft
Microsoft

Re: Showing Previous Month /Current Month Values in Future 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] )
    )

showing_previous_month

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Prodosh Frequent Visitor
Frequent Visitor

Re: Showing Previous Month /Current Month Values in Future Month values

Hi @v-jiascu-msft

Thanks for your Reply. this logic is working fine.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,387)