cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klehar
Helper IV
Helper IV

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

Hi,

 

 

QuarterSalesOutput
FY21Q116NA
FY21Q211NA
FY21Q315NA
FY21Q411NA
FY22Q115NA
FY22Q218NA
FY22Q32020
FY22Q41818

 

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
danextian
Community Champion
Community Champion

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

danextian_0-1654124538702.png

 

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




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

9 REPLIES 9
Tahreem24
Super User
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))
Capture.JPG

 

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian
Community Champion
Community Champion

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

danextian_0-1654124538702.png

 

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




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
CNENFRNL
Super User
Super User

Use an adapted fiscal quarter calendar,

CNENFRNL_0-1654073614036.png


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!

@CNENFRNL I dont think that answered my question

danextian
Community Champion
Community Champion

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?




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

@danextian I updated the question

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors