Show results in nexth month

[Anonymous]

Dear pbi users,

I am trying to show the results of a month in the next month. Please see the screen below.

I am using the following formula. 

1. Nog te werken per dag (m-1) = CALCULATE([1. Nog te werken p dag],PREVIOUSMONTH(Datum[Datum]))

As you can see (blue arrow) this goes fine for the first month, and then the values are not correct anymore. The date value in my calculate function is also marked as the date table in my report.

Anyone knows whats going wrong?

[Anonymous]

@v-xuding-msft Did you had time to take a look at the pbix file?

Thanks in advance.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late back.

Please try this:

Measure =
VAR next =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Datum'[Datum] ),
            'Datum'[Datum] < MAX ( 'Datum'[Datum] )
        ),
        'Datum'[Datum]
    )
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( Datum[Datum] ),
        "Next", next,
        "A", [1. Nog te werken p dag]
    )
RETURN
    MAXX ( FILTER ( t, Datum[Datum] = [Next] ), [A] )

[Anonymous]

Works perfectly, thanks a lot.

[v-xuding-msft]

You're welcome. Glad it works for you.😊

[amitchandak]

@Anonymous , Really strange.

Is Datanum is date table?

If not make sure date is coming from a date table that has been marked as date .

example

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

LMTD Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSMONTH('Date'[Date]))

If still you face the issue. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[parry2k]

@Anonymous does the datum column in the visual from your calendar table?

[Anonymous]

@parry2k Yes, this date column is the date value from the date table. 

@parry2k  or @amitchandak Can one of you guys speak with me on skype so i can share my screen and show you my file?

[parry2k]

@Anonymous I can but in an hour. 

[Anonymous]

Alright, thank you. 

My skype name: Guus Duker, or e-mail guus.duker@bastrucks.com

[Anonymous]

@parry2k @amitchandak, Today is possible for you?

Thanks in advance.

[v-xuding-msft]

Hi @Anonymous ，

You could try this firstly:

Measure =
VAR next =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) ),
        'Table'[Date]
    )
RETURN
    CALCULATE ( [1. Nog te werken p dag], 'Table'[Date] = next )

If it can't help, please share a dummy pbix file with us. Then we can test. (For CSS team, it is prohibited to use the personal contact to help customers.)

[parry2k]

@Anonymous formula looks correct, wondering what is the measure expression.

[Anonymous]

@parry2k the measure is:

1. Nog te werken p dag = DIVIDE([1. Uren gewerkt (Over)],[1. Werkbare dagen (Over)])

So its a divide function for the columns in front.