Forum Discussion

H1r0ka's avatar
H1r0ka
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi H1r0ka,

 

Could you please share you sample data to me?

 

Regards,

Frank

H1r0ka's avatar
H1r0ka
Frequent Visitor
7 years ago

Hello v-frfei-msft,

 

Thank you very much for your support.

Datedimensions is date table.

DateDayMonthYear
1-Sep-18192018
2-Sep-18292018
3-Sep-18392018
4-Sep-18492018
5-Sep-18592018

SalesTable contains sales date.

[Actual] is calculationfield of ( [Local Price]/[Rate]) and [Actual Total] is calculation field of (Sum [Actual]).

SalesKeyDateCustomerIDLocal PriceRateActual
0011-Sep-179901200011018.2
0021-Oct-179902300011526.1
0033-Sep-189901500012041.7
0043-Sep-1899031000012083.3
0056-Sep-189903300012025.0

 

Rank Table is list of rank.

RankType
0>=, 1000<Minor
5000>, <20000General

 

Thank you in advance,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    Please show your expected result.

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      H1r0ka
      Frequent Visitor
      7 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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