Forum Discussion
Var Formula error--Datesbetween
Hello,
I'm trying to add 'datesbetween' for the following formular. However I could not get any value.
Could you please support how to modify it?
Column 2 = VAR sales_total =
calculate([Actual Total],DATESBETWEEN(DateDimensions[Date],nextday(SAMEPERIODLASTYEAR(LASTDATE(DateDimensions[Date]))),LASTDATE(DateDimensions[Date])))
RETURN
IF(
AND (sales_total >= 0, sales_total < 1000 ),
"Minor",
IF(
AND ( sales_total > 5000, sales_total < 20000 ),
"General",
) )
Hi H1r0ka,
As we know, measures could not be added to Goup / X-axis in visuals, So here we need to create a calculated column in SalesTable.
measu1 = VAR sales_total = calculate([Actual Total],DATESBETWEEN(DateDimensions[Date],nextday(SAMEPERIODLASTYEAR(LASTDATE(DateDimensions[Date]))),LASTDATE(DateDimensions[Date]))) RETURN IF( AND (sales_total >= 0, sales_total < 50 ), "Minor", IF( AND ( sales_total > 50, sales_total < 20000 ), "General" ) )For more details, please check the attachment.
Regards,
Frank
7 Replies
Hello v-frfei-msft,
Thank you very much for your support.
Datedimensions is date table.
Date Day Month Year 1-Sep-18 1 9 2018 2-Sep-18 2 9 2018 3-Sep-18 3 9 2018 4-Sep-18 4 9 2018 5-Sep-18 5 9 2018 SalesTable contains sales date.
[Actual] is calculationfield of ( [Local Price]/[Rate]) and [Actual Total] is calculation field of (Sum [Actual]).
SalesKey Date CustomerID Local Price Rate Actual 001 1-Sep-17 9901 2000 110 18.2 002 1-Oct-17 9902 3000 115 26.1 003 3-Sep-18 9901 5000 120 41.7 004 3-Sep-18 9903 10000 120 83.3 005 6-Sep-18 9903 3000 120 25.0 Rank Table is list of rank.
Rank Type 0>=, 1000< Minor 5000>, <20000 General Thank you in advance,
Hi,
Please show your expected result.
