cancel
Showing results for
Did you mean:
Highlighted
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

## Re: Formula today -1 month

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] ),
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.
Established Member

## Re: Formula today -1 month

Try

8 REPLIES 8
Community Support Team

## Re: Formula today -1 month

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

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

## Re: Formula today -1 month

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] ),
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.
Established Member

Try

Frequent Visitor

## Re: Formula today -1 month

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!

Frequent Visitor

## Re: Formula today -1 month

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

Thank you!

Established Member

## Re: Formula today -1 month

Sure Kris, add the text in bold below:

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

Regards,

Mike

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 🙂

Announcements

#### 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?

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

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

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors