How to build a measure calculates previous year data but shown in the same row of current year?

[MiaSunny]

Hi, 

I am working on a visual to show a line chart with 2 lines for cy and py cumulative sales overlapped to compare the trends like below: 

 

And i built the data in power BI like this-- the last column is my target but i made only sales_accu_py column: 

There is a year-quarter slicer and when the slicer changed , the measures sales_accu_cy and sales_accu_py get the cumulative sales data in the past 2 qts and 2 qts one year before that, then my line chart shows 2 lines far away from each other

Is it possible to make a measure get the result like my last column, the py data could be in the same row as the cy data then we could make the line chart as mentioned on top?( Or a better plan than this to make the visual )

yearQt	sales	sales_accu	sales_accu__cy	sales_accu_py	Expected result py
2023-1	100	100
2023-2	200	300		200
2023-3	300	500		500
2023-4	400	700
2024-1	150	550
2024-2	250	400	250		200
2024-3	350	600	600		500
2024-4	450	800

Thank you! 

[mizan2390 · Accepted solution]

hi @MiaSunny 
baising on your latetst info, can you try this one

CumulativeCY = 
// 1. Capture the exact date currently evaluated on the DateTable visual axis
VAR CurrentAxisDate = MAX ( 'DateTable'[Date] )

// 2. Define your rolling boundaries 
// (Retaining your -9 months logic, though EDATE is often safer for month math)
VAR CYStart = DATE ( YEAR ( CurrentAxisDate ), MONTH ( CurrentAxisDate ) - 9, 1 )
VAR CYEnd   = DATE ( YEAR ( CurrentAxisDate ), MONTH ( CurrentAxisDate ), 1 )

// 3. Use CALCULATE to override the context and sum the defined window
VAR sumQ =
    CALCULATE (
        SUM ( QL_Summary[tQL] ),
        REMOVEFILTERS ( QL_Summary ), // Ensures no accidental cross-filtering interrupts the accumulation
        QL_Summary[QLDate] >= CYStart,
        QL_Summary[QLDate] <= CYEnd
    )

RETURN
    sumQ

TestPY = 
// 1. Capture the DateTable axis date
VAR CurrentAxisDate = MAX ( 'DateTable'[Date] )

// 2. Shift the boundaries back by exactly 1 year (12 months)
VAR PYStart = EDATE ( DATE ( YEAR ( CurrentAxisDate ), MONTH ( CurrentAxisDate ) - 9, 1 ), -12 )
VAR PYEnd   = EDATE ( DATE ( YEAR ( CurrentAxisDate ), MONTH ( CurrentAxisDate ), 1 ), -12 )

// 3. Compute the PY accumulation
VAR sumPY =
    CALCULATE (
        SUM ( QL_Summary[tQL] ),
        REMOVEFILTERS ( QL_Summary ),
        QL_Summary[QLDate] >= PYStart,
        QL_Summary[QLDate] <= PYEnd
    )

RETURN
    sumPY

If your use of MONTH(...) - 9 was a workaround attempt to force the cumulative total to reset on January 1st of the current year (a standard Year-To-Date), you can make the DAX much cleaner and safer across different years by directly anchoring CYStart to January 1st: VAR CYStart = DATE ( YEAR ( CurrentAxisDate ), 1, 1 )

If it is intentionally meant to be a rolling 10-month window, the measures provided above is supposed to handle it perfectly.

if this solves your problem, please mark this as solved and give me a kudos.

if you have any issue, @me so that I don't lose this thread. 

[MiaSunny]

I would like to have 4 quarters for CY and also for PY, so the startdate could be 9 months ago from selected month ( included as the last qt of cy ) then we will have 4 quarters for the trend. 

This did helped me a lot although not directly by copying and pasting, and give me a better understanding on date slicing through DAX. Appreciate for all the posts and suggestions! 

[mizan2390]

hi @MiaSunny 

The PBIX file would have been better. However can you please try this code to see whether this solves your problem?

py = 
// 1. Identify the latest date in the current visual node (e.g., end of 2024-2)
VAR CurrentAxisDate = MAX('Calendar'[Date])

// 2. Shift that boundary back mathematically by exactly 12 months
VAR PYCutoffDate = EDATE(CurrentAxisDate, -12)

// 3. Compute the continuous cumulative total up to the PY cutoff
VAR PYCumulative = 
    CALCULATE(
        [sales], // Replace with your base sales measure
        REMOVEFILTERS('Calendar'), // Clears the 2024 visual context so it doesn't restrict the dates
        'Calendar'[Date] <= PYCutoffDate // Re-evaluates for all time up to the PY cutoff
    )

VAR HasCYData = NOT ISBLANK([sales_accu__cy])

RETURN
    IF(HasCYData, PYCumulative)

if this solves your problem, please mark this as solved and give me a kudos.

if you have any issue, @me so that I don't lose this thread. 

[MiaSunny]

Hi @mizan2390 , thank you for the response. Here is my testing result: 

I read the code and returned the parameters you created, and get your logic of the calculation, but the result is just the sales in that month. Due to the security rules in my company i cannot share the file here, but i could paste all the code below: 

Only one table in Power query as datasource and i named it as QL_Summary

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5BCsAwCAXRu7gORI029CyS+1+j+TRF7PYthokg6dKV1amRM9NqQZZ0HZpJ85Bw2v3ZS7ZJvMRA6iUGGl5jMPMSG4jVM5DWM9D4ncEMth4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QulifiedLending_ECIP_DatebyMonth = _t, tQL = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"QulifiedLending_ECIP_DatebyMonth", type date}, {"tQL", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"QulifiedLending_ECIP_DatebyMonth", "QLDate"}})
in
#"Renamed Columns"

I created a Datetable for the sample by one line ( in my project i created one by Calendar(2020-1-1, today()) 

DateTable = VALUES(QL_Summary[QLDate])

The datetable isn't related to my QLsummary table, or my CY won't work will show one row only instead of the 4 qts. 

That's all I have and thank you again for your help! 

[MiaSunny]

Thank you for reading and replying this.

I checked some posts by @mizan2390 and tried these functions still not solved.It's always using the py date row.

It's a different situlation from https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Previous-Year-Measure-for-Line-Chart... since the data selected could be in a different year so i cannot use DATESYTD. 

[Gabry]

Hello @MiaSunny ,

sure it's possible. Do you have a calendar table right? Then you can write the measure for PY like this:

Sales Accu PY  =
CALCULATE(
    Sum(sales accu),
    SAMEPERIODLASTYEAR('Date'[Date])
)

[MiaSunny]

this doesn't work or did i miss something? just use sameperiodlastyear will get the result just like my accu_py not as expected. 

[MiaSunny]

I think my sales accu isn't helpful here, since the cy and py data is according to the selected yearquarter, instead of grab the data directly from that column. thank you !