Different calculated measures into a table

[crln-blue]

Hello! I'm really new to Power BI so still learning the ropes.

I have different calculated measures:

• Current Year - Type A
• Current Year - Type B
• Current Year - Type C
• Past Year - Type A
• Past Year - Type B
• Past Year - Type C

I use this calculated measures in my line graphs. My ultimate goal is to have a dynamic column name (for the years) and since it's not possible, I searched for other ways. My goal is to make a seperate table with this with the ff values:

Types	Values
2019 Type A	Past Year - Type A value
2019 Type B	Past Year - Type B value
2019 Type C	Past Year - Type C value
2020 Type A	Current Year - Type A value
2020 Type B	Current Year - Type B value
2020 Type C	Current Year - Type C value

Edit: The above table will be used in a line chart. I just want it to have a dynamic title so user will no longer maintain it much. This is related to my other post.

Here is my sample data where my calculated measures are based from:

Order Date	Values	Type
04/08/2019	435	A
05/07/2019	68	A
11/09/2019	2	B
04/09/2019	45	C
10/23/2019	12	C
01/01/2020	3	A
05/18/2020	23	B
06/17/2020	12	B
06/17/2020	2	C
08/19/2020	12	C
07/03/2020	34	C

Advance thanks!

[v-eqin-msft]

Hi @crln-blue ,

According to my understand, you want to calculate the difference between Current Month and Last Month dynamically , right?

You could use the following steps or  take a look at my pbix file here.

1.Create a YearMonth Column

YearMonth =
YEAR ( 'Table1'[Order Date] ) * 100
    + MONTH ( 'Table1'[Order Date] )

2. Create a table for Date Slicer

DateSlicer =
ALLSELECTED ( Table1[YearMonth] )

3.Calculate the difference:

difference =
VAR _sele =
    SELECTEDVALUE ( DateSlicer[YearMonth] )
VAR _diff =
    _sele - MAX ( 'Table1'[YearMonth] )
VAR _sum =
    IF ( _diff = 0 || _diff = 1 || _diff = 89, SUM ( 'Table1'[Values] ), BLANK () )
VAR currMinusPre =
    CALCULATE (
        SUM ( 'Table1'[Values] ),
        FILTER ( Table1, 'Table1'[YearMonth] = _sele )
    )
        - CALCULATE (
            SUM ( 'Table1'[Values] ),
            FILTER (
                Table1,
                'Table1'[YearMonth] = _sele - 1
                    || 'Table1'[YearMonth] = _sele - 89
            )
        )
RETURN
    IF ( HASONEVALUE ( 'Table1'[YearMonth] ), _sum, currMinusPre )

My visualizations look like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@crln-blue ,Output format is not clear. See if the attached file after signature can help

You can connect to a date table and use time intelligence for this year vs last year.

Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

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



Appreciate your Kudos.

[crln-blue]

Hello @amitchandak , thank you for the help and to the links! I can compute the LYTD and YTD but what I'm aiming for is to be used it to a line chart, not in a matrix chart. I apologize for the confusion on the output, I forgot to put it on my post.

[amitchandak · Accepted solution]

@crln-blue , See attached if that line visual works

[crln-blue]

Hello @amitchandak , thanks again for your answer. I'll replicate it on my end and will get back to you for further questions. Thanks!