Formula today -1 month

[Anonymous]

Hello!

I need your help with an M formula in Power Query for Power BI. I want to do a Month To Day vs. Previous Month To Day comparison e.g. 01.09.2019-20.09.2019 vs. 01.08.2019-20.08.2019.

I can not sum single days, because some metrics have to be net values for a respective time frame.

For the previous month to day value I need a formula that is something like “today- 1 month” in the Power BI query. What I already got is this, which works fine, but is wrong, because it always goes back to the number of days you entered (e.g. -31).

= Cube.Transform(examplecube,

        {

            {Cube.ApplyParameter, "DateRange", {#date(2019, 1, 1), DateTime.Date(Date.From(Number.From(Date.From(DateTime.LocalNow()))-31))}},

Does anyone has an idea how to get today-1month? I treid a lot, but nothing worked out.

Thanks for any hint!

Best,

Kristina

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Today is 2019/10/18, so this month is from 2019/10/1~2019/10/18, last month is from 2019/9/1~2019/9/18

Create measures

current =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[visitor] ),
    FILTER (
        'date table',
        'date table'[year] = YEAR ( TODAY () )
            && 'date table'[month] = MONTH ( TODAY () )
            && 'date table'[Date] <= TODAY ()
    )
)

today-1_month =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[visitor] ),
    DATEADD (
        FILTER ( DATESMTD ( 'date table'[Date] ), 'date table'[Date] <= TODAY () ),
        -1,
        MONTH
    )
)

Best Regards
Maggie

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

[Anonymous · Accepted solution]

Try

= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)

[Anonymous]

Hi @Anonymous 

thank you. That is what I need. Perfect!

Do you know wether it i possible to say: Take the local now Minus 1 day and go back 1 month?

Thank you!

[Anonymous]

Sure Kris, add the text in bold below:

Date.AddMonths(DateTime.Date(DateTime.LocalNow()+#duration(-1,0,0,0)),-1)

BTW, power query has a whole lot of date, datetime, datetimezone and duration functions that can be found at the link below.

https://docs.microsoft.com/en-us/powerquery-m/date-functions 

Regards,

Mike

[Anonymous]

That is awesome. Thanks a lot @Anonymous 

Yeah, I found that document as well, but everything I did resulted in errors 🙂

[v-juanli-msft]

Hi @Anonymous 

First please note if you want to compare the values of current month(1st to today) with values of last month,

since today and current month is dynamic, i would suggest you to create measures.

In Data modeling, Create measures

Measure_current =
CALCULATE (
    SUM ( 'Table'[value] ),
    DATESBETWEEN (
        'date table'[Date],
        DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
        TODAY ()
    )
)

Measure_lastmonth =
CALCULATE (
    SUM ( 'Table'[value] ),
    DATESBETWEEN (
        'date table'[Date],
        DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
        DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
    )
)

Best Regards
Maggie

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

ff

[Anonymous]

Hi Maggie,

thanks a lot for your help! I thought about it as well. The problem is, I can not sum the values day by day. I want to display Unique Visitors for a website for Month To Day. That value is different to a summation of the Unique Visitors by single days because within a month a person, that comes multiple times on a site is only counted once (=net value).

Therefore, I need to do an adjustment directly in the query.

Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Today is 2019/10/18, so this month is from 2019/10/1~2019/10/18, last month is from 2019/9/1~2019/9/18

Create measures

current =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[visitor] ),
    FILTER (
        'date table',
        'date table'[year] = YEAR ( TODAY () )
            && 'date table'[month] = MONTH ( TODAY () )
            && 'date table'[Date] <= TODAY ()
    )
)

today-1_month =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[visitor] ),
    DATEADD (
        FILTER ( DATESMTD ( 'date table'[Date] ), 'date table'[Date] <= TODAY () ),
        -1,
        MONTH
    )
)

Best Regards
Maggie

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

[Anonymous]

Hi @v-juanli-msft 

thank you very much for your help. That helps me as well with another problem I am facing.

Thank you!