cancel
Showing results for
Did you mean:  Helper IV

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

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

1 ACCEPTED SOLUTION  Super User

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 Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
9 REPLIES 9  Super User

@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)) Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis  Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper IV

@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  Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com  Super User

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 Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Super User

Use an adapted fiscal quarter calendar, Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!  Helper IV

@CNENFRNL I dont think that answered my question  Super User

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?

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Helper IV

@danextian I updated the question Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  