cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Rolling 3 month sum, divide by value across tables

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
Highlighted
Super User III
Super User III

Re: Rolling 3 month sum, divide by value across tables

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

Highlighted
Helper II
Helper II

Re: Rolling 3 month sum, divide by value across tables

Hi @MFelix ,

Thank you so much for your help. It works!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors