Want to show NA for Sales numbers that are from previous quarters

[klehar]

Hi,

Quarter	Sales	Output
FY21Q1	16	NA
FY21Q2	11	NA
FY21Q3	15	NA
FY21Q4	11	NA
FY22Q1	15	NA
FY22Q2	18	NA
FY22Q3	20	20
FY22Q4	18	18

I want to create a measure such that

When I'm in the past quarters I should get "NA". For all other cases (for present and future quarters) I want to see the exact sales. The output column is mentioned in the table above.

How would you know if the quarter is current or not?

The quarter definition goes like this:

If month = 11 12 1 then Q1

If month = 2 3 4 then Q2

If month = 5 6 7then Q3

If month = 8 9 10 then Q4

Now we are in quarter Q3 and I want to show "NA" for all values that fall before FY22Q3

[Tahreem24]

@klehar Try this columns and Measure.

Column Num = MID(('Table (2)'[Quarter]),3,2)&RIGHT(('Table (2)'[Quarter]),1)

Column Rank = RANKX(ALL('Table (2)'),'Table (2)'[Column Num],,DESC)

Final Measure  = CALCULATE(SUM('Table (2)'[Sales]),FILTER('Table (2)','Table (2)'[Column Rank]<=2))

[Ashish_Mathur]

Hi,

In the Source data, do you have a Date column?

[klehar]

@Ashish_Mathur yes i do

That date column is derived.

For Q1 the date is fixed to 1/11/Year

Q2 : 1/2/Year

Q3 : 1/5/Year

Q4 : 1/8/Year

[Ashish_Mathur]

Well then please share the source data table with a propwer date column.

[danextian · Accepted solution]

Hi @klehar ,

I would prefer to use a separate dates table as that will make the calculation easier with but for your use case, try doing these calculated column/measure approach:

Qtr = 
VAR __YR = MID('Table'[Quarter],3,2)
VAR __QTR = RIGHT('Table'[Quarter],1)
return
__YR & __QTR
// a calculated column for the FY and qtr as a number

Value (Calc Column) = 
VAR __MONTH =
    MONTH ( TODAY () )
VAR __QTR =
    SWITCH (
        TRUE (),
        __MONTH IN { 11, 12, 1 }, 1,
        __MONTH IN { 2, 3, 4 }, 2,
        __MONTH IN { 5, 6, 7 }, 3,
        __MONTH IN { 8, 9, 10 }, 4,
        BLANK ()
    )
VAR __FY =
    RIGHT ( IF ( __MONTH >= 11, YEAR ( TODAY () ) + 1, YEAR ( TODAY () ) ), 2 )
VAR __FYQTR = __FY & __QTR
RETURN
    IF ( 'Table'[Qtr] < __FYQTR, "NA", FORMAT ( 'Table'[Value], "#" ) )

Value (Measure) = 
VAR __MONTH =
    MONTH ( TODAY () )
VAR __QTR =
    SWITCH (
        TRUE (),
        __MONTH IN { 11, 12, 1 }, 1,
        __MONTH IN { 2, 3, 4 }, 2,
        __MONTH IN { 5, 6, 7 }, 3,
        __MONTH IN { 8, 9, 10 }, 4,
        BLANK ()
    )
VAR __FY =
    RIGHT ( IF ( __MONTH >= 11, YEAR ( TODAY () ) + 1, YEAR ( TODAY () ) ), 2 )
VAR __FYQTR = __FY & __QTR
VAR __NotNA =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Qtr] >= __FYQTR )
    )
RETURN
    IF ( ISBLANK ( __NotNA ), "NA", __NotNA )

Here's a snapshot of the result

Sample PBIX for your reference https://drive.google.com/file/d/1OfJ-tEzJ52MefiCIWjzjIzGqFscPQcUf/view?usp=sharing 

[CNENFRNL]

Use an adapted fiscal quarter calendar,

[klehar]

@CNENFRNL I dont think that answered my question

[danextian]

Hi @klehar 

How would you know if a quarter is current or not? Is it based on a slicer selection or based on today's date?

[klehar]

@danextian I updated the question