cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Change calculation between Current year Qtr1 and previous year Qtr4

Hi All-

Im looking to calculate change between Current year Qtr1 and previous year Qtr4. Thank you

1 ACCEPTED SOLUTION
Community Support

Hi @RilwanFlame ,

Try to use DATEADD function like so:

``````Difference =
VAR PreQuar_ =
CALCULATE ( SUM ( 'Table'[Value] ), DATEADD ( 'Table'[Date], -1, QUARTER ) )
VAR ThisQuar_ =
SUM ( 'Table'[Value] )
RETURN
ThisQuar_ - PreQuar_
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 REPLIES 9
Community Support

Hi @RilwanFlame ,

Try to use DATEADD function like so:

``````Difference =
VAR PreQuar_ =
CALCULATE ( SUM ( 'Table'[Value] ), DATEADD ( 'Table'[Date], -1, QUARTER ) )
VAR ThisQuar_ =
SUM ( 'Table'[Value] )
RETURN
ThisQuar_ - PreQuar_
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

You can utilize the Row Totals to display the change between the last two quarters. Assuming the original measure is SUM ( Table[Sales] ) and that you have a year-quarter column in your date table in the format YYYYQ

then [Sales Amount] can be modified to something like

``````Sales Amount =
VAR CurrentYearQuarter =
MAX ( 'Date'[Year Quarter] )
VAR CurrentSales =
SUM ( Table[Sales] )
VAR LastSales =
CALCULATE (
SUM ( Table[Sales] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Year Quarter] = CurrentYearQuarter
)
VAR PerviousSales =
CALCULATE (
SUM ( Table[Sales] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Year Quarter] = CurrentYearQuarter - 1
)
RETURN
IF (
HASONEVALUE ( 'Date'[Year Quarter] ),
CurrentSales,
PerviousSales - LastSales
)``````

Helper III
Hi Tamerj-
So, i tried to follow your script  which i have below but getting and error:

Quarterly Chg Diff =
VAR CurrentYearQuarter =
MAX ( 'Calendar'[YearQuarter] )
VAR CurrentSale =
SUM ('ICC Reports'[%] )
VAR LastSale =
CALCULATE (
SUM ('ICC Reports'[%] ),
REMOVEFILTERS ('Calendar'),
'Calendar'[YearQuarter] = CurrentYearQuarter
)
VAR PerviousSale =
CALCULATE (
SUM ('ICC Reports'[%] ),
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[YearQuarter] = CurrentYearQuarter - 1
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[YearQuarter] ),
CurrentSale,
PerviousSale - LastSale
)
Super User

YearQuarter column should be of integer data type. I sent you a code for a datetable in a private message. Please apply the code to create a new date table. The only thing you need to do is to replace Sales[Order Date] with name of the date column in your sales table. Then you can use the [Year Quarter Number] column in the measure's code

Helper III

Hi Tamerj-

Thank you for your response. I do not have a date table (calendar table) created. I am using the Date field coming directly from my dataset and it does not have year-quarter.

What if i want to create a calendar(date) table with a year-quarter in it and then with a relationship to my actual dataset table.

Thank you

Thank you

Super User

Yes you haveto have  a DateTable with at least date, year, quarter and year-quarter. Then build one to many relationship with the sales fact table. In your table visual use the date attributes from the DateTable. Then it should work.
creating such table shall not be an issue. If your having trouble creating it please let me know

Helper III

Yes  please, i will need help creating that.

So far, i have the below created

"Year", Year([Date]),
"MonthNo", Month([Date]),
"Month", Format([Date],"mmm"),
"QuarterNo", Quarter([Date]),
"YearQuarter", "Q" & Quarter([Date]) & " " & Year([Date]))

Super User

great

"YearQuarter"Quarter([Date]) + 10 * Year([Date])

Helper III

Hi Tamerj-

Im stil working on getting the difference between the current quarter and previous quarter.

After creating the Date table, some DAx measure created got messed up. Like the Quarter To Dtae Change (QTD Chg) and Year To Date Change (YTD Chg). it start populating 0.00%

Before creating the Date table, this use to be my formula for QTD Chg:

QTD Chg=
IF(
ISFILTERED('ICC Reports'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_QUARTER = CALCULATE(SUM('ICC Reports'[Decimal]), DATEADD('ICC Reports'[Date].[Date], -1, QUARTER))
RETURN
DIVIDE(SUM('ICC Reports'[Decimal]) - __PREV_QUARTER, __PREV_QUARTER)
)

And for YTD Chg:

IF(

ISFILTERED('ICC Reports'[Date]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_YE = CALCULATE(SUM('ICC Reports'[Decimal]), DATEADD('ICC Reports'[Date].[Date], -Quarter(SELECTEDVALUE('ICC Reports'[Date])), QUARTER))

RETURN

DIVIDE(SUM('ICC Reports'[Decimal]) - __PREV_YE, __PREV_YE)

)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors