Create Opening Measure - 2 Columns of Dates to equal inactive Date table Value from Slicer

[ML222]

Hi,

Here I have:

- Fact Table with 2 Date columns ( DIM_DAY_KEY & VALUE_DIM_DAY_KEY)
- A Date Table with an active relationship to the Fact table

- A Comparison Date table with an Inactive relationship to the Fact Table

My Canvas looks like this:

Comparison Date = Compairson Date Table with Inactive Relationship
Report Date = Date Table with Active Relationship

The calulation I require is to work out the Opening Coverage.
I need to use a Divide Function.

For the numerator I need the Sum of Value Where:
DIM_DAY_KEY = The Value of Comparision Date
&& VALUE_DIM_DAY_KEY = The Value of Comparision Date

For the denominator I need the Opening Value which is quite simply:

I have tried a number of different DAX formulas but they either return blank, or timeout. The actual table is very large.
Here's a couple I've tried:

Any Idea's on where I am going wrong in my DAX?

Thank you

[amitchandak]

@ML222 , Refer the approach I used

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Select data between months - https://youtu.be/nEt7dT3Tfv4

[ML222 · Accepted solution]

Hi @amitchandak,

Thank you. That video helped although I did not acheive the result with your method.
In my instance; I need to apply the 'FromDate' to where both the Primrary date and the Secondary Date (from my Fact table) equal the selected 'From Date' Value. When trying to solve with just one calculate, the DAX would return blanks.

I was able to get it to work using the following DAX:

This works. Performance seems good.
If you see a way to get this solved in one calculate, that would be ideal. However, I have managed to figure that one out yet.