How to get previous trimester

BI_diva — Mon, 30 Jan 2023 23:01:32 GMT

Hi,

I need to make a comparison to the previous trimester (months 1-4; 5-8; 9-12) for the trimester that is selected in the graph by the end user.

After trying several things, I am not getting the value (DISTINCTCOUNT) I am looking for.

Please advice.

CountToT =
VAR _Trimester = MAX(DIM_Date[Trimester])
VAR _Calculation1 =
CALCULATE(
DISTINCTCOUNT('Fact'[FactKey]),
FILTER(
ALLSELECTED(DIM_Date),
DIM_Date[Trimester] = _Trimester -1
)
)
RETURN
_Calculation1

Desired result = 757

I do get the correct result for the same trimester last year by using this formula:

CALCULATE(
DISTINCTCOUNT('Fact'[FactKey]),
DATEADD(DIM_Date[Date],-1,YEAR)
)

Re: How to get previous trimester

amitchandak — Tue, 31 Jan 2023 01:58:11 GMT

BI_diva , Create a new column in Dim_date , Mark it as date table too

Year Trimester = year([Date]) * 100 + [Trimester]

Then have rank column

Trimester Rank = RANKX('dim_Date','Date'[Year Trimester],,ASC,Dense)

These measures can help. Change the sum expression as per need
Trimester Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Trimester Rank]=max('dim_Date'[Trimester Rank])))
Trimester Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Trimester Rank]=max('dim_Date'[Trimester Rank])-1))

This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Year]=max('dim_Date'[Year]) && 'dim_Date'[Trimester] = Max('dim_Date'[Trimester]) ))
Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Year]=max('dim_Date'[Year])-1 && 'dim_Date'[Trimester] = Max('dim_Date'[Trimester])))

Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

topic How to get previous trimester in DAX Commands and Tips

How to get previous trimester

Re: How to get previous trimester