Var Formula error--Datesbetween

[H1r0ka]

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",
          ) )

[v-frfei-msft]

Hi @H1r0ka,

Could you please share you sample data to me?

Regards,

Frank

[H1r0ka]

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,

[v-frfei-msft]

Hi @H1r0ka,

Based on your data, I made one sample as below. Actually that should be the issue of your formula, you need to remove the last comma of your formual.

measu =
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 pbix as attached. If it doesn't meet your requirement, kindly share your excepted  result to me.

Regards,

Frank

[H1r0ka]

Hello

Thank you very much for your solution. The table is wish to create.

I also need the following fannel chart with the formula data.

How to creat that?

[v-frfei-msft · Accepted solution]

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

[H1r0ka]

Hello,

Thank you very much for your support! I got the funnel chart.

[Ashish_Mathur]

Hi,

Please show your expected result.