Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Calculate IN

Hello, I'm pretty new to writing DAX.   I've created Measure that CALCULATE SUM of VALUE IN specified PERIOD   Here is a formula: Plan III'18 = CALCULATE( SUM(Table[Plan]); Table[Quarter] ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    One sample for your reference.

     

    1. Create a calculated table as below.

     

    Table = UNION(VALUES(Table1[Quarter]),VALUES(Table1[Quarter Prev]))

    2. Creat a measure to get the result as we need.

     

    Measure = 
VAR _sele =
    SELECTEDVALUE ( 'Table'[Quarter] )
VAR _pre =
    SWITCH (
        _sele,
        "IV'2018", "III'2018",
        "III'2018", "II'2018",
        "II'2018", "I'2018",
        "I'2018", "IV'2017"
    )
RETURN
    IF (
        ISBLANK ( _sele ),
        BLANK (),
        CALCULATE ( SUM ( Table1[Plan] ), FILTER ( Table1, Table1[Quarter] = _pre ) )
    )

    Also please find the pbix as attached.

     

    Regards,

    Frank

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

Hi Anonymous,

 

One sample for your reference.

 

1. Create a calculated table as below.

 

Table = UNION(VALUES(Table1[Quarter]),VALUES(Table1[Quarter Prev]))

2. Creat a measure to get the result as we need.

 

Measure = 
VAR _sele =
    SELECTEDVALUE ( 'Table'[Quarter] )
VAR _pre =
    SWITCH (
        _sele,
        "IV'2018", "III'2018",
        "III'2018", "II'2018",
        "II'2018", "I'2018",
        "I'2018", "IV'2017"
    )
RETURN
    IF (
        ISBLANK ( _sele ),
        BLANK (),
        CALCULATE ( SUM ( Table1[Plan] ), FILTER ( Table1, Table1[Quarter] = _pre ) )
    )

Also please find the pbix as attached.

 

Regards,

Frank