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 calcu...
  • 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.

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