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
DCELL
Helper III
Helper III

Rolling 3 month sum, divide by value across tables

Hello,

My problem is a bit difficult to explain in words alone, so here's a spreadsheet to make it clearer:

DCELL_1-1601486458586.png

 

I have 2 fact tables. Columns with black text are columns that I have, and columns in blue are ones that I don't have.
The only number I care about obtaining is the one on the far right (in blue). My dataset is small so creating calculated columns isn't a problem if necessary.

I have created a rolling 3 month sum measure as follows:

 

Rolling 3 month sum = 
    CALCULATE(
        SUM(Table1[COGS]),
        DATESINPERIOD(Table1[Date],
        LASTDATE(Table1[Date]), -3, MONTH)
    )

 

 

but when dividing 'Table2'[Balance] by the result in this measure, it needs to divide it by country, not as a whole.

Any help is appreciated as always 🙂

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @DCELL ,

 

I created a model with the following tables:

  • COGS
  • BALANCE
  • Country (Dimension table)
  • Date (Dimension table)

The dimension table are use to make a relationship between the COGS and Balance table then create the following two measures:

 

Rolling 3 month sum =
IF (
    DATEDIFF (
        MINX (
            DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
            'Calendar'[Date]
        );
        MAXX (
            DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
            'Calendar'[Date]
        );
        DAY
    ) < 61;
    BLANK ();
    CALCULATE (
        SUM ( COGS[COGS] );
        DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH )
    )
)

Balance 3 Months =
IF (
    [Rolling 3 month sum] = BLANK ();
    BLANK ();
    SUM ( Balance[Balance] ) / [Rolling 3 month sum] * 91
)

 

See result below and in attach file:

MFelix_0-1601550513134.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @DCELL ,

 

I created a model with the following tables:

  • COGS
  • BALANCE
  • Country (Dimension table)
  • Date (Dimension table)

The dimension table are use to make a relationship between the COGS and Balance table then create the following two measures:

 

Rolling 3 month sum =
IF (
    DATEDIFF (
        MINX (
            DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
            'Calendar'[Date]
        );
        MAXX (
            DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
            'Calendar'[Date]
        );
        DAY
    ) < 61;
    BLANK ();
    CALCULATE (
        SUM ( COGS[COGS] );
        DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH )
    )
)

Balance 3 Months =
IF (
    [Rolling 3 month sum] = BLANK ();
    BLANK ();
    SUM ( Balance[Balance] ) / [Rolling 3 month sum] * 91
)

 

See result below and in attach file:

MFelix_0-1601550513134.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

Thank you so much for your help. It works!

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.