Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RilwanFlame
Helper III
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

 

RilwanFlame_0-1651249057469.png

 

1 ACCEPTED SOLUTION
Icey
Community Support
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.

View solution in original post

9 REPLIES 9
Icey
Community Support
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.

tamerj1
Super User
Super User

Hi @RilwanFlame 

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
    )

 

 

Hi Tamerj-
So, i tried to follow your script  which i have below but getting and error:
 
RilwanFlame_0-1651440497485.png

 




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
)

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

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 

@RilwanFlame 

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

Yes  please, i will need help creating that. 

 

So far, i have the below created

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

 

@RilwanFlame 

great 

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

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% 

 

RilwanFlame_0-1651438128478.png

 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)

)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors