Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    Try

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

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-juanli-msft
        Community Support
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    Try

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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