Forum Discussion

Aks-1's avatar
Aks-1
Frequent Visitor
6 years ago

Tabular Model: I am trying to create a Measure or Calculated column that holds the selected Period

Hi,             I am looking for help in Tabular Model as the requirement is to design the Measure or Calculated column that will hold the selected year and month which can then be used to calculate...
Need Help
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

Hi Aks-1 ,

 

You can get cumulative value in the range of slicer using DAX below.

 

Measure1=
Var FromDate = CALCULATE(MIN(Slicer[Date]),ALLSELECTED(Slicer[Date]))
Var ToDate = CALCULATE(MAX(Slicer[Date]),ALLSELECTED(Slicer[Date]))
Return
CALCULATE(SUM(Table1[sales]), FILTER(ALLSELECTED(Table1), Table1[Date]>=FromDate &&Table1[Date]<=ToDate))

 

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Aks-1's avatar
    Aks-1
    Frequent Visitor
    6 years ago

    Hi v-xicai ,

                        I have  the listed following filters

    The intention here is to pass the Loss year value to the measure which calculates the total based on month and year to  derive the respective Quarter.

     

     

    = IF( IF(VALUE(LEFT(LossTriangle_POC[ACCTG_Period],4))>= 2017 && VALUE(LEFT( LossTriangle_POC[ACCTG_Period],4)) <= VALUE(LEFT(LossTriangle_POC[EVAULATIONPERIOD],4)) , VALUE(RIGHT(LossTriangle_POC[EVAULATIONPERIOD],2)) + ( ROUNDUP(VALUE( RIGHT(LossTriangle_POC[ACCTG_Period] ,2)/3),0) - ROUNDUP( VALUE(RIGHT(LossTriangle_POC[EVAULATIONPERIOD] ,2)/3),0)) * 3 + (VALUE(LEFT(LossTriangle_POC[ACCTG_Period],4)) - 2017 ) * 12 ,0) >= VALUE(RIGHT(LossTriangle_POC[EVAULATIONPERIOD],2)) , IF(VALUE(LEFT(LossTriangle_POC[ACCTG_Period],4))>= 2017 && VALUE(LEFT( LossTriangle_POC[ACCTG_Period],4)) <= VALUE(LEFT(LossTriangle_POC[EVAULATIONPERIOD],4)) , VALUE(RIGHT(LossTriangle_POC[EVAULATIONPERIOD],2)) + ( ROUNDUP(VALUE( RIGHT(LossTriangle_POC[ACCTG_Period] ,2)/3),0) - ROUNDUP( VALUE(RIGHT(LossTriangle_POC[EVAULATIONPERIOD] ,2)/3),0)) * 3 + (VALUE(LEFT(LossTriangle_POC[ACCTG_Period],4)) - 2017 ) * 12 ,0) , 0 )

     

    The Sample data should be something like this:

     

     

     

    Thank you so much for the response.

  • Aks-1's avatar
    Aks-1
    Frequent Visitor
    6 years ago

    Hi v-xicai ,

                            That didn't work.What I am tyring is to calculate a Measure that holds Inception to date, losses depending on selected selected attribute, in my case it is Loss year.

     

    This should do the trick but does consider the loss year.

     

    VAR MaxDate = MAX (DimDate[Date] )
    RETURN
    if( ISBLANK( CALCULATE (
    SUM(LossTriangle_POC[WINS_Medical_Paid]),
    DimDate[Date]<= MaxDate,
    ALL ( DimDate[Date])
    )) , 0 , CALCULATE (
    SUM(LossTriangle_POC[WINS_Medical_Paid]),
    DimDate[Date]<= MaxDate,
    ALL ( DimDate) ))

    Any help highly appreciated.

     

     

    Thanks.