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 ca...
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)

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

    Hi v-juanli-msft 

     

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

     

    Thank you!