Two columns related to date calendar

[MauroBote]

Hi friends.

I have the following table:

And I need to show it like this:

The columns "PLAN AC" and "REAL AC" are running totals of PLAN and REAL.

Thanks for your help!

[v-lili6-msft · Accepted solution]

HI  @MauroBote 

For your case, just create two inactive relationship with date table.

Then create four measure as below:

PLAN = CALCULATE(SUM('Table'[PESO]),USERELATIONSHIP('Date'[Date],'Table'[PECHA PLAN]),'Table'[PECHA PLAN]<>BLANK())

REAL = CALCULATE(SUM('Table'[PESO]),USERELATIONSHIP('Date'[Date],'Table'[FECHA REAL]),'Table'[FECHA REAL]<>BLANK())

PLAN AC = CALCULATE([PLAN],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))

REAL AC = CALCULATE([REAL],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))

Result:

Regards,

Lin

[Greg_Deckler]

You could connect both dates to the Calendar table. This would create a inactive relationship which you could get around using USERELATIONSHIP.

That being said, it would be very easy to just leave the tables completely unrelated and do this:

PLAN AC =
  VAR __Table = FILTER('Table1','Table1'[FECHA PLAN]<='Table2'[CALENDARIO])
RETURN
  SUMX(__Table,[PESO])

REAL AC =
  VAR __Table = FILTER('Table1','Table1'[FECHA REAL]<='Table2'[CALENDARIO])
RETURN
  SUMX(__Table,[PESO])

[amitchandak]

You can connect both tables to the same date calendar and use userelation and cross filter as per need. Please find my example on mu blog on similar lines

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...