Forum Discussion
DKM_83
2 years agoRegular Visitor
Variance % selected week- DAX
Hi Team, if I selecting a two week then i want to show the var% between the value. Thanks, DK
- 2 years ago
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
INDEX function (DAX) - DAX | Microsoft Learn
Sales measure: = VAR _sales = SUM ( Data[Sales] ) VAR _weekselectcondition = COUNTROWS ( DISTINCT ( Week[Week] ) ) = 2 VAR _totalcolumn = NOT ( HASONEVALUE ( Week[Week] ) ) VAR _salesone = CALCULATE ( SUM ( Data[Sales] ), INDEX ( 1, VALUES ( Week[Week] ), ORDERBY ( Week[Week], ASC ) ) ) VAR _salestwo = CALCULATE ( SUM ( Data[Sales] ), INDEX ( 1, VALUES ( Week[Week] ), ORDERBY ( Week[Week], DESC ) ) ) VAR _percentage = FORMAT ( DIVIDE ( _salestwo - _salesone, _salesone ), "#0.00%" ) RETURN IF ( _weekselectcondition && _totalcolumn, _percentage, _sales )
Jihwan_Kim
2 years agoSuper User
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
INDEX function (DAX) - DAX | Microsoft Learn
Sales measure: =
VAR _sales =
SUM ( Data[Sales] )
VAR _weekselectcondition =
COUNTROWS ( DISTINCT ( Week[Week] ) ) = 2
VAR _totalcolumn =
NOT ( HASONEVALUE ( Week[Week] ) )
VAR _salesone =
CALCULATE (
SUM ( Data[Sales] ),
INDEX ( 1, VALUES ( Week[Week] ), ORDERBY ( Week[Week], ASC ) )
)
VAR _salestwo =
CALCULATE (
SUM ( Data[Sales] ),
INDEX ( 1, VALUES ( Week[Week] ), ORDERBY ( Week[Week], DESC ) )
)
VAR _percentage =
FORMAT ( DIVIDE ( _salestwo - _salesone, _salesone ), "#0.00%" )
RETURN
IF ( _weekselectcondition && _totalcolumn, _percentage, _sales )