Forum Discussion
Year over Year comparison
I am facing a problem, i want to compare previous year with current year till Date. i am having previous year data and current year till 17th september 2019. i had used a DAX formula
Net_Sales_LY = VAR LastDayAvailable =
CALCULATE (
MAX ( 'table1'[Day] ),
ALL ( 'table1' )
)
VAR FirstDayInSelection =
MIN ( 'DateTable'[Date] )
VAR ShowData =
(FirstDayInSelection <= LastDayAvailable)
VAR Result =
IF (
ShowData,
CALCULATE (
[Net_Sales],
SAMEPERIODLASTYEAR ( 'DateTable'[Date] )
)
)
RETURN Result
while using this dax function it compare previous year with current year i.e, i must consider Previous Year values till 17th september 2018 as 324866 since our data has values only upto 17th september 2019. But the result shows that it takes the values for full month of September 2018 and displays the total value as 633102
How can i solve it
Anonymous ,
You can modify the measure as below and check if it can meet your requirement:
Net_Sales_LY = VAR LastDayAvailable = CALCULATE ( MAX ( 'table1'[Day] ), ALL ( 'table1' ) ) VAR FirstDayInSelection = MIN ( 'DateTable'[Date] ) VAR ShowData = ( FirstDayInSelection <= LastDayAvailable ) VAR Result = IF ( ShowData, CALCULATE ( [Net_Sales], SAMEPERIODLASTYEAR ( DATE ( 2019, 9, 17 ) ) ) ) RETURN ResultCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Anonymous ,
You can modify the measure as below and check if it can meet your requirement:
Net_Sales_LY = VAR LastDayAvailable = CALCULATE ( MAX ( 'table1'[Day] ), ALL ( 'table1' ) ) VAR FirstDayInSelection = MIN ( 'DateTable'[Date] ) VAR ShowData = ( FirstDayInSelection <= LastDayAvailable ) VAR Result = IF ( ShowData, CALCULATE ( [Net_Sales], SAMEPERIODLASTYEAR ( DATE ( 2019, 9, 17 ) ) ) ) RETURN ResultCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.