How to get previous trimester

[BI_diva]

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)
)

[amitchandak]

@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