Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

undefined

Hi all,

please I'm trying to calculate a measure for the last month same period and display it on a matrix visual , where I'm having the sales per day .

i.e if today is 14.05.2024 , I want to calculate sum sales for the previous month ( from the 1st to 14 / 04 .2024 

Please any help !! 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Thanks for the reply from aduguid.

     

    Anonymous , the following testing is for your reference:

     

    My sample:

     

    Create a measure as follows

     

    Measure = 
VAR _lmonthfirstday = EOMONTH(TODAY(), -2) + 1
VAR _lmonth = EDATE(TODAY(), -1)
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Date] >= _lmonthfirstday && [Date] <= _lmonth))
RETURN
_sum

     

     

    Output:

     

    Since I don't know what your data structure is like, I have only tested up to this point, so if the method has problems with your data, please provide some data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    You can use a DAX query for timeframes.

     

    Calendar Timeframe = 
VAR _today_date =                   'Properties'[Today Date] // or TODAY() or DATE(2023, 07, 15)
VAR _month_start =                  DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_start_py =               DATE( YEAR(_today_date) - 1, MONTH(_today_date), 01 )
VAR _today_date_py =                DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) )

VAR _result = 
    UNION (
      ADDCOLUMNS (CALENDAR ( _month_start, _today_date ),       "Timeframe", "MTD",     "Timeframe Order", 1 )
    , ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ), "Timeframe", "MTD_PY",  "Timeframe Order", 2 )
    )

RETURN
_result

     

    Then create the relationship to your calendar table

    After that you can use one measure to calculate each timeframe. 

    I have more examples of timeframes in my GitHub project here

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I tried it but it does not work dear ,

    actually , i have created a calendar spanning from 2014 to 2024 and used it as a filter to segment the dates (linked it to my table).

    and i have created three measures for this purpose. However, I'm encountering difficulties with the "LAST MONTH'S SALES (SAME PERIOD)" measure. 

     

     

    my table is as below :

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Thanks for the reply from aduguid.

     

    Anonymous , the following testing is for your reference:

     

    My sample:

     

    Create a measure as follows

     

    Measure = 
VAR _lmonthfirstday = EOMONTH(TODAY(), -2) + 1
VAR _lmonth = EDATE(TODAY(), -1)
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Date] >= _lmonthfirstday && [Date] <= _lmonth))
RETURN
_sum

     

     

    Output:

     

    Since I don't know what your data structure is like, I have only tested up to this point, so if the method has problems with your data, please provide some data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.