Measure with relationship between two dimension tables

[Anonymous]

I would like to create measures that reference two date related dimension tables.

I have a fact table and then DIM_Date and DIM_AccountingPeriod.

DIM_Date is a pure date table and I mark it as date table - I would like to use this for time intelligence and eg cumulative measures at a day level. So it has largely a Calculation function. DIM_Date links to the Fact table with a Date_Key.

DIM_AccountingPeriod contains rows for each day (like DIM_Date), but it has a column that indicates which CalendarMonth it belongs to as well as the ReportingMonth eg ReportingDateEnd is 25/01/2023 and CalendarDateEnd is of course 31/01/2023. The reason I dont use this as date table is because there are multiple systems/companies in the database and they can have different ReportingDateEnds. I would like to use it to display the ReportingMonth or CalendarMonth with the relevant measure. DIM_AccountingPeriod links to the Fact table with a TimeID.

An inner join between would look something like this:

1) can I model it in a way (star or snowflake etc) so that DIM_Date and DIM_AccountingPeriod works together and 

2) how can I write the DAX so that both those dimension tables work together

eg if the visual contains ReportingDateEnd (from DIM_AccountingPeriod) and Date (from DIM_Date), then I would like my cumulative measure to work. My measure looks as follows at the moment, but does not work. I was thinking of incorporating USERELATIONSHIP as a solution, but have not found the correct place to use it.

VAR CumulativeTotal =

CALCULATE (
[Sales],
FILTER (
ALLSELECTED ( 'DIM_Date' ),
'DIM_Date'[Date] <= MAX ( 'DIM_Date'[Date] )
),

FILTER (
ALLSELECTED ( 'DIM_AccountingPeriod' ),
'DIM_AccountingPeriod'[ReportingDateEnd]
<= MAX ( 'DIM_AccountingPeriod'[ReportingDateEnd] )
)
)

RETURN
IF ( ISBLANK ( [Sales] ), BLANK (), CumulativeTotal )

Can someone advise on the appropriate modelling and/or DAX coding to get this to work?  

Sample data -

https://docs.google.com/spreadsheets/d/1nIcmKu90E8_oqLxApcAeOARZo7UcehAp/edit?usp=sharing&ouid=10554...

[amitchandak]

@Anonymous , I think better you filter accounting slicer as per need from date table, and join both with fact , that should solve purpose

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

or use treatas create a measure in accounting period table to filter it from date table and use it as visual level filter

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[Anonymous]

to clarify, I would like to be able to achieve this in Power BI

I would like the same measure to aggregate the daily total in the context of either the ReportingDateEnd or MonthEnd

what should the measure look like?

what should the relationships between fact and the two dimension tables look like?

the fact table has relationship to DIM_Date using Date_Key and to DIM_AccountingPeriod via TimeID. how can I get both the Date from DIM_date to work with either DIM_AccountingPeriod in the same visual?

[Anonymous]

hi @amitchandak 

Just checking if you have anything further to add after my response?