Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 the cumulative count for following year losses to get the loss traingle.
I have a FromDate and AsofDate
When I make a selection on the FromDate ,my report should start from that month qtr and calculate cummulative losses as of that Period .
I am trying the formula something like this but not getting the desired result .
= var mndt = MONTH([Minimum date])
var qrtdt = ROUNDUP( MONTH([Minimum date])/3 , 0)
var yrdt = YEAR([Minimum date])
return
if ( Acctng_Period[Year]>= Acctng_Period[minyear] && Acctng_Period[Quarter] >= FORMAT( ROUNDUP( MONTH([Minimum date])/3 , 0),"0") ,
MONTH([Minimum date])+
(Acctng_Period[Quarter]- ROUNDUP( MONTH([Minimum date])/3 , 0)* 3 )+
Acctng_Period[Year] - YEAR([Minimum date])* 12
mndt + ( Acctng_Period[Quarter] - qrtdt ) * 3 + ( Acctng_Period[Year] - yrdt) * 12
, 0)
Any help is highly appreciated!
Thanks.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
39 | |
33 | |
31 | |
25 |