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

Formula today -1 month

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Formula today -1 month

Hi @KRIS45910 

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
    )
)
Capture1.JPG
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.

View solution in original post

mcybulski Established Member
Established Member

Re: Formula today -1 month

Try

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

View solution in original post

8 REPLIES 8
Community Support Team
Community Support Team

Re: Formula today -1 month

Hi @KRIS45910 

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

Capture11.JPG

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

KRIS45910 Frequent Visitor
Frequent Visitor

Re: Formula today -1 month

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!

Community Support Team
Community Support Team

Re: Formula today -1 month

Hi @KRIS45910 

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
    )
)
Capture1.JPG
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.

View solution in original post

mcybulski Established Member
Established Member

Re: Formula today -1 month

Try

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

View solution in original post

KRIS45910 Frequent Visitor
Frequent Visitor

Re: Formula today -1 month

Hi @mcybulski 

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!

KRIS45910 Frequent Visitor
Frequent Visitor

Re: Formula today -1 month

Hi @v-juanli-msft 

 

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

 

Thank you!

mcybulski Established Member
Established Member

Re: Formula today -1 month

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

KRIS45910 Frequent Visitor
Frequent Visitor

Re: Formula today -1 month

That is awesome. Thanks a lot @mcybulski 

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors