Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
Im looking for a solution in dax that the value will only multiple by from the frequency indicated.
Here's my table.
My problem is how to in dax can i have this kind of output if i put it in a trend chart.
Here's the target output in table.
Thanks in advance!
Solved! Go to Solution.
Hi @John_Dozen34999 ,
According to my understanding , you want to display
Employee1 ——100, 100*2 ,
Employee4 ——300, 300*2 , 300*3 ...
not just display the result
Employee1 ——100,200,
Employee4 ——300 ,600 ,900 ... right?
Please modify the Cummulative measure:
Cummulative =
VAR _value =
IF (
[Measure] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
),
[Measure]
)
)
VAR _minValue =
MINX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
),
[Measure]
)
RETURN
IF (
ISINSCOPE ( 'Calendar'[Month-Day] )
&& _value <> BLANK (),
IF (
_value = _minValue,
_minValue,
_minValue & " * "
& CONVERT ( DIVIDE ( _value, _minValue ), STRING )
),
IF (
ISINSCOPE ( 'Calendar'[QuarterName] ),
BLANK (),
SUMX ( 'Calendar', [Measure] )
)
)
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @John_Dozen34999 ,
Please follow these steps:
1. Add a Month-Day column:
Month-Day = FORMAT([Month Start],"MMM-DD")
2.Create a Calendar table:
Calendar =
CROSSJOIN (
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"QuarterName", "Q" & FORMAT ( [Date], "Q" ),
"Month", MONTH ( [Date] ),
"Month-Day", FORMAT ( [Date], "MMM-21" )
)
),
VALUES ( 'Table'[Employee] )
)
3. Add columns to Calendar table:
Amount Column = LOOKUPVALUE('Table'[Amount],[Employee],[Employee],'Table'[Month-Day],[Month-Day])
Frequency Column = LOOKUPVALUE('Table'[Frequency],[Employee],[Employee])
4. Now try the following formula to create measure:
Measure =
VAR _value =
CALCULATE (
SUM ( 'Calendar'[Amount Column] ),
ALLEXCEPT ( 'Calendar', 'Calendar'[Employee] )
)
VAR _minMonth =
CALCULATE (
MIN ( 'Calendar'[Month] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Frequency Column] = MAX ( 'Calendar'[Frequency Column] )
&& FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () )
)
)
RETURN
SWITCH (
MAX ( 'Calendar'[Frequency Column] ),
"Quarterly",
IF (
MAX ( 'Calendar'[Month] ) >= _minMonth
&& MOD ( MAX ( 'Calendar'[Month] ) - _minMonth, 3 ) = 0,
_value,
BLANK ()
),
"Annual", FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () ),
"Monthly", IF ( MAX ( 'Calendar'[Month] ) >= _minMonth, _value, BLANK () ),
"BiAnnual",
IF (
MAX ( 'Calendar'[Month] ) = MAXX ( ALL ( 'Calendar' ), [Month] )
|| MAX ( 'Calendar'[Month] ) = _minMonth,
_value,
BLANK ()
)
)
5. Use ISINSCOPE() to get cummulative total in Matrix:
Cummulative =
VAR _value =
IF (
[Measure] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
),
[Measure]
)
)
RETURN
IF (
ISINSCOPE ( 'Calendar'[Month-Day] ),
_value,
IF (
ISINSCOPE ( 'Calendar'[QuarterName] ),
BLANK (),
SUMX ( 'Calendar', [Measure] )
)
)
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your help. however im curious on the total in a monthly basis. It is not showing total per month
Hi @John_Dozen34999 ,
According to my understanding , you want to display
Employee1 ——100, 100*2 ,
Employee4 ——300, 300*2 , 300*3 ...
not just display the result
Employee1 ——100,200,
Employee4 ——300 ,600 ,900 ... right?
Please modify the Cummulative measure:
Cummulative =
VAR _value =
IF (
[Measure] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
),
[Measure]
)
)
VAR _minValue =
MINX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
),
[Measure]
)
RETURN
IF (
ISINSCOPE ( 'Calendar'[Month-Day] )
&& _value <> BLANK (),
IF (
_value = _minValue,
_minValue,
_minValue & " * "
& CONVERT ( DIVIDE ( _value, _minValue ), STRING )
),
IF (
ISINSCOPE ( 'Calendar'[QuarterName] ),
BLANK (),
SUMX ( 'Calendar', [Measure] )
)
)
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@John_Dozen34999 , Create a date table, with half year, month, qtr. and then create a measure like
sum(Table[Amount])+0 and then try to display this
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |