How to apply most recent data from the current month and to apply in to the future months

[Anonymous]

Dear Everyone,

I am trying to apply the values from the most recent month from Sales Table by products and to apply the same values for the future months.

I have Sales table with Sales [Dates] and connected with Dates [Dates] for applying current and future periods.

As Sales table is updated with new month values the measures should again update the figures for future months.

Any help is highly appreciated!

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

If so,you have to delete the relationship between the two tables;

Then create a measure as below:

Measure =
VAR _maxdate =
    MAXX ( ALL ( 'Sales Table' ), 'Sales Table'[Sales Date] )
RETURN
    IF (
        MONTH ( MAX ( 'Dates'[Period] ) ) > MONTH ( _maxdate ),
        CALCULATE (
            SUM ( 'Sales Table'[Sales Amount] ),
            FILTER (
                'Sales Table',
                MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( _maxdate )
                    && YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( _maxdate )
            )
        ),
        CALCULATE (
            SUM ( 'Sales Table'[Sales Amount] ),
            FILTER (
                'Sales Table',
                YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( MAX ( 'Dates'[Period] ) )
                    && MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( MAX ( 'Dates'[Period] ) )
            )
        )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous · Accepted solution]

Hello Kelly,

Yes it works ! Thank you very much !

[amitchandak]

@Anonymous ,

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

MTD Max=

var _today = maxx(allselected('Sales'), 'Sales'[sales date])
var _min = eomonth(_today ,-1)+1
var _max = _today
return
CALCULATE(sum('Sales'[Sales Amounr]), FILTER(ALL('order'),'order'[Date] >= _min && 'order'[Date] <=_max ) )

Try measures like

final measure =

var _today = maxx(allselected('Sales'), 'Sales'[sales date])

return 

if(isblank([MTD Sales]) && Max(Date[Date]) =_today , [MTD Max],[MTD Sales])

[Anonymous]

Dear @amitchandak ,

Thank you for quick reply ! Just to clarify 'Order' table and Order[Date]. Where these should come from ?

I have Sales and Dates table with columns [Date]. I am not sure about Order. Did you mean Dates table and [Date] column? I have applied this but I still get the table with only the last month figures. It does not show future months even though the time slicer is set with future months included....

[v-kelly-msft]

Hi  @Anonymous,

Could you pls provide some dummy data with expected output for test?

Best Regards,
Kelly

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

[Anonymous]

Hello Kelly,

Here is the link below with the sample file.  I have the most recent data of sales ending with July 2021. I would like to replicate the same values from the month of July to the future months connected with the Dates table. 

https://drive.google.com/file/d/1TJtor3ppAPirpTbbIX7Hh0dQrxtzRmmG/view?usp=sharing 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

If so,you have to delete the relationship between the two tables;

Then create a measure as below:

Measure =
VAR _maxdate =
    MAXX ( ALL ( 'Sales Table' ), 'Sales Table'[Sales Date] )
RETURN
    IF (
        MONTH ( MAX ( 'Dates'[Period] ) ) > MONTH ( _maxdate ),
        CALCULATE (
            SUM ( 'Sales Table'[Sales Amount] ),
            FILTER (
                'Sales Table',
                MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( _maxdate )
                    && YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( _maxdate )
            )
        ),
        CALCULATE (
            SUM ( 'Sales Table'[Sales Amount] ),
            FILTER (
                'Sales Table',
                YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( MAX ( 'Dates'[Period] ) )
                    && MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( MAX ( 'Dates'[Period] ) )
            )
        )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous · Accepted solution]

Hello Kelly,

Yes it works ! Thank you very much !

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Glad to help.

Would you pls check my signature?🙂

Best Regards,
Kelly

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