Search code examples
powerbidaxssas-tabular

Count from the selection to the last 3 quarters backawards

I have 2 tables in Oracle that I use in SSAS Tabular model :

MyFactTable DimDate

The relationship beteween them is 1 to many MyFactTable[FileExtractDate] to DimDate[Date]

A simple count of the RAF ID is _count_Client_Distinct = COUNT(MyFactTable[Client ID])

now I want to calculate based on the selection the count of the RAF ID and the last 3 quarters

For example:

if I select FileExtractDate= 12/10/2021 the visual of chart bars will give me the count of the Client ID of the Q4 since 12/10/2021 belongs to the 4th quarter and displays the count of Client ID in the 3 other quarters (Q1, Q2, Q3)

if I select FileExtractDate= 09/02/2021 the visual of chart bars will give me the count of the Client ID of the Q1 since 09/10/2021 belongs to the 1st quarter and displays the count of Client ID in the 3 other quarters (Q2, Q3, Q4) but for 2020

without any selection it will take the max date and show the count for the quarter of the max date and the last 3 quarters

I tried the following:

Prev 3 Qtrs = 
VAR _End = STARTOFQUARTER( 'Date'[Date] ) - 1
VAR _Start =
    CALCULATE(
        MIN( 'Date'[Date] ),
        DATEADD(
            DATEADD(
                'Date'[Date],
                -1,
                YEAR
            ),
            1,
            QUARTER
        )
    )
VAR _Result =
    CALCULATE(
        [Count],
        'Date'[Date] >= _Start
            && 'Date'[Date] <= _End
    )
RETURN
    _Result

But when I select a date it is showing only the result for the quarter to which that date belongs.

Solution

  • When using a slicer or filter, it is not possible to display rows outside of the sliced/filtered range. What this means is you need a separate table for your visual (chart X-axis) and the one for the date slicer.

    Here is my suggestion - create a duplicate Date table and maybe call it "Date Filter".

    Date Filter = 'Date'

    This new table does not need any relationships with any other table, so ensure none exist.

    Then for your measure:

    Prev 3 Qtr = 
  CALCULATE(
    COUNTROWS('MyFactTable'),
    KEEPFILTERS(
      DATESINPERIOD('Date'[Date], ENDOFQUARTER('Date Filter'[Date]), -4, QUARTER)
    )
  )

    enter image description here

    To clarify:

    • Slicer is from the 'Date Filter' table
    • Visual X-axis is from the 'Date' table