Cross join with Running Total

[Anonymous]

Hi there,

Please take a look at the attachment and see what's wrong with my calculations?

Basically I've try to crrate a Cross Join table between Goals Amt per Department and Monthly Calendar with Running Totals

Thanks,

Oded Dror

 

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Spoiler

Greg,

Sorry about that here is the explanation to the attachment
I have two tables, 1 Department with Yearly Goal per department , and 2 Calendar with Month Name and End Of Month as date (Jan - Dec , 01/01/2020 - 12/31/2020)
I created cross join between these two tables with the following code

CJ Table = CROSSJOIN(VALUES('Goal'[Dept]),VALUES('Calendar'[Month]))
I got 12 months per department everything works just fine.
Then I calculate the monthly Goal (Year / 12)

Monthly Contract = CALCULATE(

DIVIDE(sum('Goal'[Amt]),12),

Filter('Goal','Goal'[Dept] = SELECTEDVALUE('CJ Table'[Dept]))) //-- Works just fine
When I'm trying to calculate the running total the expected results will be 10,000,000 for Jan until 120,000,000 for Dec.
I created measure (based on Running total in New Quick Measure)

RT =

CALCULATE(

    [Monthly Contract], // This is the Monthly Amt I calcualted before which works fine

 FILTER('Calendar','Calendar'[EOM] <= MAX('Calendar'[EOM]))

) 
From some reson it not produce the expected results (it show the Monthly contract values) - I need help here

Dept / Month	Monthly Goal	Running total (expected)
12 - Jan	10000000	10000000
12 - Feb	10000000	20000000
12 - Mar	10000000	30000000

Month
Jan	01/31/2020
FEB	02/29/2020

Dept	Yearly Goal
12	120000000
14	80000000

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the measure:

RT =
SUMX (
    FILTER (
        ALLEXCEPT ( 'CJ Table', 'CJ Table'[Dept] ),
        'CJ Table'[EOM] <= MAX ( 'CJ Table'[EOM] )
    ),
    [Monthly Contract]
)

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

Best Regards,

Dedmon Dai

[Anonymous]

Dedmon,

Thank you so much It works.
Oded Dror