Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CALvalier75
New Member

Dynamic Multi-Row Card return 3 & 6 month projections

Hi,

I need a DAX code for a multi-row card that will return the 3 & 6 month projections from a table I have called "BWR".  Below is an example of the table BWR and in this example I would want to return November's $1,370,473 and February's $ 3,054,069.  My goal is to string them together to read above a chart "3 month risk is $1,370,473 and, 6 month risk is $3,054,069".  I've tried running 1) a FIRSTNONBLANK formula but, it won't work knowing that several of months in the Real Risk USD data have blanks, 2) the other options I thought of using would be referencing the dates but, it has to be dynamic as the the 3-6 month's will move with time or, 3) a LASTNONBLANK since the 6 month (February in this case) will always be the last nonblank since there will never be data beyond 6 months.  Any help with any of my options 1-3 or, something else would be very helpful.

 

edateReal Risk USD
2/1/2018$ 201,361
3/1/2018$ 301,321
4/1/2018$ 381,325
5/1/2018 
6/1/2018$ 901,321
7/1/2018$ 519,527
8/1/2018$ 760,286
9/1/2018 
10/1/2018 
11/1/2018$ 1,370,473
12/1/2018 
1/1/2019 
2/1/2019$ 3,054,069
3/1/2019 
4/1/2019 

 

Regards,

C

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @CALvalier75,

 

You can try to use below formula:

Rolling Total=
VAR currDate =
    MAX ( Table[Date] )
VAR roll_Three =
    CALCULATE (
        SUM ( Table[Real Risk USD] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
                && [date] <= currDate
        )
    )
VAR roll_Six =
    CALCULATE (
        SUM ( Table[Real Risk USD] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate ) )
                && [date] <= currDate
        )
    )
RETURN
    "Rolling 3 month:" & roll_Three & ",Rolling 6 month:" & roll_Six

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @Xiaoxin Sheng,

What you provided works but, I noticed that it sums the data in the table to try and create a rolling 3 and 6 month instead of calling the actual value.  In actuality the data in the table at 3 and 6 projections are already summed which is causing a double count of the data.  What would be a replacement formula for the below line in your code that would return the actual value instead of aggregation?

SUM ( Table[Real Risk USD] ),

 Thanks,

CAL

HI @CALvalier75,

 

If your table already stored 'sum up' value, you can use max function to replace sum function to get max value.

Rolling Total=
VAR currDate =
    MAX ( Table[Date] )
VAR roll_Three =
    CALCULATE (
        MAX( Table[Real Risk USD] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
                && [date] <= currDate
        )
    )
VAR roll_Six =
    CALCULATE (
        MAX( Table[Real Risk USD] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate ) )
                && [date] <= currDate
        )
    )
RETURN
    "Rolling 3 month:" & roll_Three & ",Rolling 6 month:" & roll_Six

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.