Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Super User!
@klehar Try this columns and Measure.
Hi,
In the Source data, do you have a Date column?
@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.
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
Proud to be a 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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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!
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |