Get performance (MTD / YTD / Last Month / Last Year) from price table

[frank666]

Hi, I have a table with daily forex rates for a couple of currenies.

I would like to create a table to show currencies performance in % (based on th last date in the table) over several periods in a matrix as follows :

Period	USD	EUR	GBP
MTD	1.2%	-2%	3.4%
Last Month	0.4%	-1.1%	0.3%
QTD	...	...	...
Last Quarter	...	...	...
YTD	...	...	...
Last Year

Any idea how to do this ? I guess it should be done with"measures" but I do not know how.

Thanks a lot for help

Frank

[aduguid · Accepted solution]

You can use a DAX query to produce the timeframes. Then you would only need one measure per currency for the calculation.

Calendar Timeframe = 
VAR _today_date =                   TODAY()
VAR _yesterday_date =               _today_date - 1
VAR _week_start =                   _today_date - WEEKDAY ( _today_date, 2 )
VAR _week_end =                     _today_date - WEEKDAY ( _today_date, 2 ) + 6
VAR _month_start =                  DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_end =                    EOMONTH( _today_date, 0)
VAR _quarter_start =                DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 )
VAR _quarter_end =                  EOMONTH(EDATE(_quarter_start, 2), 0) 
VAR _fiscal_year =                  YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start =            DATE( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =              DATE( _fiscal_year, 06, 30)
VAR _tomorrow_date =                IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1)
VAR _calendar_year =                YEAR(_today_date)
VAR _calendar_year_start =          DATE( _calendar_year , 01, 01)
VAR _calendar_year_end =            DATE( _calendar_year, 12, 31)
VAR _previous_month_start =         IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1))
VAR _previous_month_end =           DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start,0)))
VAR _previous_quarter_start =       EDATE(_quarter_start, -3) 
VAR _previous_quarter_end =         EOMONTH(EDATE(_quarter_start, -1), 0) 
VAR _previous_fiscal_year_start =   DATE( _fiscal_year - 2, 07, 01)
VAR _previous_fiscal_year_end =     DATE( _fiscal_year - 1, 06, 30)
VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01)
VAR _previous_calendar_year_end =   DATE( _calendar_year - 1, 12, 31)

VAR _result = 
    UNION (
      ADDCOLUMNS (CALENDAR ( _today_date, _today_date),                                     "Timeframe", "Today",                     "Timeframe Order", 1 )
    , ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date),                             "Timeframe", "Yesterday",                 "Timeframe Order", 2 )
    , ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ),                              "Timeframe", "Previous Week",             "Timeframe Order", 3 )
    , ADDCOLUMNS (CALENDAR ( _week_start, _week_end ),                                      "Timeframe", "Current Week",              "Timeframe Order", 4 )
    , ADDCOLUMNS (CALENDAR ( _week_start, _today_date ),                                    "Timeframe", "WTD",                       "Timeframe Order", 5 )
    , ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ),                  "Timeframe", "Previous Month",            "Timeframe Order", 6 )
    , ADDCOLUMNS (CALENDAR ( _month_start, _month_end ),                                    "Timeframe", "Current Month",             "Timeframe Order", 7 )
    , ADDCOLUMNS (CALENDAR ( _month_start, _today_date ),                                   "Timeframe", "MTD",                       "Timeframe Order", 8 )
    , ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ),              "Timeframe", "Previous Qtr",              "Timeframe Order", 9 )
    , ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ),                                "Timeframe", "Current Qtr",               "Timeframe Order", 10 )
    , ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ),                                 "Timeframe", "QTD",                       "Timeframe Order", 11 )
    , ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ),      "Timeframe", "Previous Year",             "Timeframe Order", 12 )
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ),                        "Timeframe", "Current Year",              "Timeframe Order", 13 )
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ),                             "Timeframe", "YTD",                       "Timeframe Order", 14 )
    , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ),                            "Timeframe", "Rest of Year",              "Timeframe Order", 15 )
    , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ),  "Timeframe", "Previous Calendar Year",    "Timeframe Order", 16 )
    , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ),                    "Timeframe", "Current Calendar Year",     "Timeframe Order", 17 )
    , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ),                           "Timeframe", "Calendar YTD",              "Timeframe Order", 18 )
    , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ),                          "Timeframe", "Rest of Calendar Year",     "Timeframe Order", 19 )
    )

RETURN
_result

[frank666]

Thanks it works like a charm! Best.

[sanalytics]

Hello @frank666 

There are two ways to achieve this kind of result.

1) With Calculation group

2) Without Calculation group ( with Field Parameter basically)

Below is the ss and attached pbix file for your reference.

you can investigate attached pbix file and achieve your desired result.

https://www.transfernow.net/dl/20240501RIGepwvZ

One quick tips : whenever you post your question, instead of providing screenshot please provide some dummy data so that we can try from our end and give your desired result.

Regards

sanalytics

If it is your solution then please like and accept it as solution

[frank666]

Thanks @sanalytics for your very detailed example. I have tried to understand it and to use it in my own use but it is too complex for my basic knowledge of pbi and dax. I would apprciate if you can drive me on the right way with my use case...

The provided pbix contains 2 tables of my data in 2 different format (CCY1 & CC2). I guess CCY is the best to use for what I need. Right.

https://www.transfernow.net/dl/202405027QXcHxwD 

Thanks again for your help.

Frank