Helper III

6 years ago

Solved

YTD Average & Average Change

Hi All,

I have a matrix setup where I need to add additional columns to indicate the changes in prior month figures, YTD average and also the change if possible of that average from one month to the next!

My table example is per below and my values in each table I have created are being generated via a filter already per example:

Measure = CALCULATE(SUM('Table'[Value]), FILTER('Table','Table'[Category]="XXX"))

Many thanks

Branch	Jan	Feb	Mar	Change from Prior Month	YTD Average	Change from Prior Average
A	5	3	6	+3	4.67	+0.67
B	24	21	19	-2	21.33	+1.17
C	16	18	21	+3	18.34	+1.34
D	64	55	57	+2	58.66	-0.84

Need Help

v-gizhi-msft

6 years ago

Hi,

According to your description, i create a sample to test:

Please try to create a column header first:

ColumnHeader = 
    UNION (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[Date].[MonthNo] ),
            "Month", FORMAT ( DATE ( 2020, [Date].[MonthNo], 1 ), "MMM" )
        ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 13, "Change from Prior Month" } } ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 14, "YTD Average" } } ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 15, "Change from Prior Average" } } )
    )

It shows:

Then create a measure:

Measure = 
VAR MaxMonth =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR LastMonth =
    MONTH ( MAX ( 'Table'[Date] ) ) - 1
RETURN
    SWITCH (
        MAX ( ColumnHeader[Month] ),
        "Jan", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 1 )
        ),
        "Feb", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 2 )
        ),
        "Mar", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 3 )
        ),
        "Change from Prior Month", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = MaxMonth )
        )
            - CALCULATE (
                SUM ( 'Table'[Values] ),
                FILTER ( 'Table', MONTH ( 'Table'[Date] ) = LastMonth )
            ),
        "YTD Average", FIXED ( AVERAGE ( 'Table'[Values] ), 2 ),
        "Change from Prior Average", FIXED (
            AVERAGE ( 'Table'[Values] )
                - CALCULATE (
                    AVERAGE ( 'Table'[Values] ),
                    FILTER ( 'Table', MONTH ( 'Table'[Date] ) <> MaxMonth )
                ),
            2
        )
    )

Apply a filter to matrix visual such as [Category]="AAA", the result shows:

Here is my test pbix file:

pbix

Hope this helps.

Best Regards,

Giotto Zhi

6 Replies

v-gizhi-msft

Community Support

6 years ago

Hi,

According to your description, i create a sample to test:

Please try to create a column header first:

ColumnHeader = 
    UNION (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[Date].[MonthNo] ),
            "Month", FORMAT ( DATE ( 2020, [Date].[MonthNo], 1 ), "MMM" )
        ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 13, "Change from Prior Month" } } ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 14, "YTD Average" } } ),
        DATATABLE ( "MonthNo", INTEGER, "Month", STRING, { { 15, "Change from Prior Average" } } )
    )

It shows:

Then create a measure:

Measure = 
VAR MaxMonth =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR LastMonth =
    MONTH ( MAX ( 'Table'[Date] ) ) - 1
RETURN
    SWITCH (
        MAX ( ColumnHeader[Month] ),
        "Jan", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 1 )
        ),
        "Feb", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 2 )
        ),
        "Mar", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = 3 )
        ),
        "Change from Prior Month", CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = MaxMonth )
        )
            - CALCULATE (
                SUM ( 'Table'[Values] ),
                FILTER ( 'Table', MONTH ( 'Table'[Date] ) = LastMonth )
            ),
        "YTD Average", FIXED ( AVERAGE ( 'Table'[Values] ), 2 ),
        "Change from Prior Average", FIXED (
            AVERAGE ( 'Table'[Values] )
                - CALCULATE (
                    AVERAGE ( 'Table'[Values] ),
                    FILTER ( 'Table', MONTH ( 'Table'[Date] ) <> MaxMonth )
                ),
            2
        )
    )

Apply a filter to matrix visual such as [Category]="AAA", the result shows:

Here is my test pbix file:

pbix

Hope this helps.

Best Regards,

Giotto Zhi

Gazsim44
Helper III
6 years ago
Many thanks Giotto - that works fantastic! 🙂

My only further question is I am using whole number values only however although I have changed the format to this for the measure the YTD Average & Change from Prior Average columns still appear as decimals? Any ideas why this is?

Thanks again,
- v-gizhi-msft
  Community Support
  6 years ago
  Hi,
  
  It is because after calculated by AVERAGE, the result will appear as decimals type.
  It is by design.
  
  Best Regards,
  Giotto Zhi

amitchandak
Super User
6 years ago
Make sure you have date calendar and you are using that in slicer. That should also have a month, month year column

YTD value = var _max = maxx(allselected('Date'),'Date'[Date]) var _min = maxx(allselected('Date'),STARTOFYEAR ('Date'[Date])) var _val = CALCULATE('Table'[Value]),filter(all('Date'),'Date'[Date]<=_max && >=_min),FILTER('Table','Table'[Category]="XXX")) return averagex(summarize('Table','Date'[Month-Year],"_sum",_val),[_sum]) LYTD value = var _max = maxx(allselected('Date'),dateadd('Date'[Date],-1,year)) var _min = maxx(allselected('Date'),STARTOFYEAR (dateadd('Date'[Date],-1,year)) var _val = CALCULATE('Table'[Value]),filter(all('Date'),'Date'[Date]<=_max && >=_min),FILTER('Table','Table'[Category]="XXX")) return averagex(summarize('Table','Date'[Month-Year],"_sum",_val),[_sum]) diff = [YTD value] -[LYTD value]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
- Gazsim44
  Helper III
  6 years ago
  Hi,
  
  Thanks for the reply although getting an error when trying to replicate your YTD value measure.
  
  For var_val = CALCULATE(.... It is not allowing me to select a table. I can insert SUM following CALCULATE which then allows this but I am getting a further error where it is flagging >= as being incorrect syntax?
  
  Thanks,
Greg_Deckler
Community Champion
6 years ago
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Forum Discussion

YTD Average & Average Change

6 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Average and Moving Average

Weekly Average

Accumulated average

moving average help

Forum Discussion

YTD Average &amp; Average Change

Fabric & Azure Data User Group Program Requirements & Benefits

Average and Moving Average

Weekly Average

Accumulated average

moving average help

YTD Average & Average Change