Forum Discussion
Helper IVPrevious quarters from selected quarter
hi all,
I want to create a quarterly report, where the user can select one quarter. In the underlying date table every quarter has a quarter offset (so q2 2024 is offset -1, q1 2024 is offset -2). I want to create different measures, where i compare the data from the selected quarter with the data from the same quarter last year and with the data from the previous quarter, and the average from the previous 4 quarters. Also, in the graphs i present, i only want to show the selected quarters and the 4 previous quarters.
I have a sales table with sales and salesdate. I have a direct relationship between salesdate and date in datetable.
So: user selects Q1 2024
Total sales: sales in quarter offset -2
Total sales previous quarter: sales in quarter offset -3
Total sales same quarter previous year: sales in quarter offset -6
Average sales previous 4 quarters: average from sales quarter, -3,-4,-5,-6
In graphs i want to present offset -6, -5, -4, -3, -2
Any suggestions for this?
Thanks in advance,
Regards, Frank
Hi frankhofmans
My solution is as follows, you can try it out.
- Create new columns “OFFSET” “QY” “sort” in SalesTable.
OFFSET = VAR CurrentYear = YEAR(TODAY())
VAR CurrentQuarter = ROUNDUP(MONTH(TODAY())/3, 0)
VAR DateYear = YEAR([salesdate])
VAR DateQuarter = ROUNDUP(MONTH([salesdate])/3, 0)
RETURN (DateYear - CurrentYear)*4 + (DateQuarter - CurrentQuarter)
QY = "Q"&QUARTER([salesdate])&" "&YEAR([salesdate])
sort = FORMAT([salesdate],"YYYYQQ") - Select “QY” column and click Sort by column “sort”.
- Creat a DateTable by DAX as follow:
DateTable = ADDCOLUMNS(
CALENDAR(MIN(SalesTable[salesdate]), MAX(SalesTable[salesdate])),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT([Date], "Q") & " " & YEAR([Date]),
"QuarterOffset",
VAR CurrentYear = YEAR(TODAY())
VAR CurrentQuarter = ROUNDUP(MONTH(TODAY())/3, 0)
VAR DateYear = YEAR([Date])
VAR DateQuarter = ROUNDUP(MONTH([Date])/3, 0)
RETURN (DateYear - CurrentYear)*4 + (DateQuarter - CurrentQuarter)
) - Create a measure by DAX as follow:
Measure =
var _dateoffset=SELECTEDVALUE(DateTable[QuarterOffset])
var _salesoffset=MAX('SalesTable'[OFFSET])
return IF(_salesoffset<=_dateoffset&&_salesoffset>=_dateoffset-4,1) - Put “sales” ”QY” ”OFFSET” of SalesTable into a new created table visual. Create a slicer and put “Quarter” of DateTable into the slicer.
-
Use “Measure” in the table visual, and apply filter to "is" "1".
- Here is my final result.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Create new columns “OFFSET” “QY” “sort” in SalesTable.
2 Replies
- AmiraBedh
Super User
Can you share your model and some data?
Hi frankhofmans
My solution is as follows, you can try it out.
- Create new columns “OFFSET” “QY” “sort” in SalesTable.
OFFSET = VAR CurrentYear = YEAR(TODAY())
VAR CurrentQuarter = ROUNDUP(MONTH(TODAY())/3, 0)
VAR DateYear = YEAR([salesdate])
VAR DateQuarter = ROUNDUP(MONTH([salesdate])/3, 0)
RETURN (DateYear - CurrentYear)*4 + (DateQuarter - CurrentQuarter)
QY = "Q"&QUARTER([salesdate])&" "&YEAR([salesdate])
sort = FORMAT([salesdate],"YYYYQQ") - Select “QY” column and click Sort by column “sort”.
- Creat a DateTable by DAX as follow:
DateTable = ADDCOLUMNS(
CALENDAR(MIN(SalesTable[salesdate]), MAX(SalesTable[salesdate])),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT([Date], "Q") & " " & YEAR([Date]),
"QuarterOffset",
VAR CurrentYear = YEAR(TODAY())
VAR CurrentQuarter = ROUNDUP(MONTH(TODAY())/3, 0)
VAR DateYear = YEAR([Date])
VAR DateQuarter = ROUNDUP(MONTH([Date])/3, 0)
RETURN (DateYear - CurrentYear)*4 + (DateQuarter - CurrentQuarter)
) - Create a measure by DAX as follow:
Measure =
var _dateoffset=SELECTEDVALUE(DateTable[QuarterOffset])
var _salesoffset=MAX('SalesTable'[OFFSET])
return IF(_salesoffset<=_dateoffset&&_salesoffset>=_dateoffset-4,1) - Put “sales” ”QY” ”OFFSET” of SalesTable into a new created table visual. Create a slicer and put “Quarter” of DateTable into the slicer.
-
Use “Measure” in the table visual, and apply filter to "is" "1".
- Here is my final result.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Create new columns “OFFSET” “QY” “sort” in SalesTable.
