Date/Time Intelligent Dax in Matrix

[Thomas-B-Hudson]

Hi All,

I have created 3 dax measures showing fill rates:

1) Overall Fill Rate - This is the fill rate for the selected date range

2) Historic Fill Rate - This is the fill rate for everything inside and outside the date range.

4) Forecast Fill Rate - This is the expected fill rate based on the two rates above.

Problem:
I want to be able to see how these 3 fill rates change over the past 2 years, so I have a matrix which brings these in and a date column (not from a table marked as date) which has a hierachy on to show Year and Qtr.

I also have a slicer with a date range in the report, so with the matrix, I have editted the interaction with the date slicer so that I can always show the previous 2 years in this matrix and all the other visuals are still filtered by this date range for the users.

You can see from the matrix (visual marked 1), this client has an Overall Fill Rate of 26%, the historic fill rate is 52% and the forecast fill rate is 48%.

However when I look at the matric (visual marked 2) for QTR 3 2022 which is the same period - 1 day as the date slicer. The Overall Fill Rate is fine, however the Historic Fill Rate and Fill Rate Forecast doesn't calculate the same. This happens for each quarter as you go back in time in the visual marked 2.

I am just wondering what I might be doing wrong. Ultimately what I want is EG:
QTR 3
Overall Fill Rate = Overall Fill Rate for that quarter. = 26%
Historic Fill Rate = Overall Fill Rate from the start of time to the end date of the quarter. = 52%
Forecast Fill Rate = Based on the numbers in this quarter what is the forecasted fill = 48%

I am not sure if this is even possible but thought I would put it to the community.

Below are the calculation I am using to mark up the 3 fill rate forecasts.

Calculations:

1) Overall Fill Rate

IF(

DIVIDE(

calculate(sumx(JobPlacement,JobPlacement[Value]),FILTER(JobOrder,JobOrder[isOpen]=0)),

CALCULATE(SUMX(JobOrder,JobOrder[numOpenings]),FILTER(JobOrder,JobOrder[isOpen]=0)),0)

>1,1,

DIVIDE(

calculate(sumx(JobPlacement,JobPlacement[Value]),FILTER(JobOrder,JobOrder[isOpen]=0)),

CALCULATE(SUMX(JobOrder,JobOrder[numOpenings]),FILTER(JobOrder,JobOrder[isOpen]=0)),0))

2) Historic Fill Rate

Fill Rate % (Historical) =

CALCULATE([Fill Rate% (Closed)],

ALL(vdimDate[activity_Date]))

3) Forecast Fill Rate

Fill Rate % Forecast =

IF(

DIVIDE(

[Forecast Placements Closed], [Forecast Positions Closed])

>1,1,

DIVIDE(

[Forecast Placements Closed], [Forecast Positions Closed]))

3.1) Forecast Placements Closed

SUMX(JobPlacement, JobPlacement[Value]) + ([#Positions Rem.] * [Fill Rate % (Historical)])

3.2) Forecast Positions Closed

Forecast Positions Closed =

SUMX('Measures Table', 'Measures Table'[#Positions Rem.]) + SUMX(JobOrder, JobOrder[numOpenings])

*Positions Rem.

#Positions Rem. = IF(CALCULATE(SUMX(JobOrder,JobOrder[numOpenings]),JobOrder[isOpen]=1)-CALCULATE(SUMX(JobPlacement,JobPlacement[Value]),JobOrder[isOpen]=1)<0,0,

CALCULATE(SUMX(JobOrder,JobOrder[numOpenings]),JobOrder[isOpen]=1)-CALCULATE(SUMX(JobPlacement,JobPlacement[Value]),JobOrder[isOpen]=1))

[Thomas-B-Hudson]

Hi @edhans 

Thanks for the tips, I always forget to use VAR and RETURN functions so this is very helpful and something I will keep in mind.

Regarding my issue.

So I have a date table which I use the activity date from across all my visuals, the date table however is not marked as a date table as this doesn't allow me to add a hierachy to the matrix, unless I use the calendar_year and quarter_nbr columns. But this gives me the same result anyway.

So I have a matrix which shows the overall position of a client for the date range give (last 11 weeks)

However, I have another matrix which also uses dates from the same date table, however I have editted the interaction of this table so that it is not filtered by the Last 11 weeks Date Slicer.

This allows me to show how the fill rates change over the past couple of Years/Quarter. The Fill Rate% (Closed) otherwise known as Overall Fill Rate calculate fine for year quarter and year.

However the historic fill rate (Fill Rate% Historical) and the forecast fille rate (Fill rate% Forecast) are giving the same return as the Fill Rate% Closed which is not right.

I would expect the Historical Fill Rate to calculate for each Year/QTR from the start of time to the end date of the given Year/QTR in the matrix.

For Fill Rate I would only expect to see a value here for the current period/period which have a value in the Positions Rem. calculation given above in those periods. But this also doesn't look to be happening as this to is the same as the Fill Rate% Closed calculation.

[edhans]

@Thomas-B-Hudson this is going to be difficult to do without some data, which I understand will not be a simple thing to put together on your end - unless you can share the model if there is nothing confidential in it.

I need to see the model, and all filters applied when measures are returning in correct results. Editing interactions further complicates things.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans]

Hi @Thomas-B-Hudson I am having a little trouble following what the problem is but a few observations.

1) You should always use the dates in the Date Table for measures, never dates in the fact table. Hide those.

2) You can greatly simplify and speed up the measures by using variables and not filtering tables. Examples

Your original measure: (I use daxformatter.com for all measures as I cannot read Excel style runon formulas.. 😊 )

Overall Fill Rate =
IF(
    DIVIDE(
        CALCULATE(
            SUMX(
                JobPlacement,
                JobPlacement[Value]
            ),
            FILTER(
                JobOrder,
                JobOrder[isOpen] = 0
            )
        ),
        CALCULATE(
            SUMX(
                JobOrder,
                JobOrder[numOpenings]
            ),
            FILTER(
                JobOrder,
                JobOrder[isOpen] = 0
            )
        ),
        0
    ) > 1,
    1,
    DIVIDE(
        CALCULATE(
            SUMX(
                JobPlacement,
                JobPlacement[Value]
            ),
            FILTER(
                JobOrder,
                JobOrder[isOpen] = 0
            )
        ),
        CALCULATE(
            SUMX(
                JobOrder,
                JobOrder[numOpenings]
            ),
            FILTER(
                JobOrder,
                JobOrder[isOpen] = 0
            )
        ),
        0
    )
)

You are calculating many things twice. Instead, do this:

Overall Fill Rate =
VAR varJobPlacement =
    CALCULATE(
        SUMX(
            JobPlacement,
            JobPlacement[Value]
        ),
        FILTER(
            JobOrder,
            JobOrder[isOpen] = 0
        )
    )
VAR varJobOrder =
    CALCULATE(
        SUMX(
            JobOrder,
            JobOrder[numOpenings]
        ),
        FILTER(
            JobOrder,
            JobOrder[isOpen] = 0
        )
    )
VAR Result =
    IF(
        DIVIDE(
            varJobPlacement,
            varJobOrder,
            0
        ) > 1,
        1,
        DIVIDE(
            varJobPlacement,
            varJobOrder,
            0
        )
    )
RETURN
    Result

Now only varJobPlacement and varJobOrder are only calculated 1 time. And never filter a table, only filter fields. So the above becomes...

Overall Fill Rate =
VAR varJobPlacement =
    CALCULATE(
        SUMX(
            JobPlacement,
            JobPlacement[Value]
        ),
        JobOrder[isOpen] = 0
    )
VAR varJobOrder =
    CALCULATE(
        SUMX(
            JobOrder,
            JobOrder[numOpenings]
        ),
        JobOrder[isOpen] = 0
    )
VAR Result =
    IF(
        DIVIDE(
            varJobPlacement,
            varJobOrder,
            0
        ) > 1,
        1,
        DIVIDE(
            varJobPlacement,
            varJobOrder,
            0
        )
    )
RETURN
    Result

That is equivalent to this (one variable only)

VAR varJobPlacement =
    CALCULATE(
        SUMX(
            JobPlacement,
            JobPlacement[Value]
        ),
        FILTER(
            ALL( JobOrder[isOpen] ),
            JobOrder[isOpen] = 0
        )
    )

So now you are only filtering the values in the isOpen field, not the entire table. On a 10,000 record table it might not matter. On a 300,000,000 record table, it is the difference between a 500ms result and an out of memory error.

Now, can you provide some more specific example of what is not calculating correctly? I think if the data is modeled properly and you are using the Date table and not date fields in the fact tables, it will be easier to get the 3 measures to return what you want.

Oh, one other thing. If you are just summing one field, you can use the SUM fuction, so SUM(JobPlacement[Value]) vs SUMX(JobPlacement, JobPlacement[Value])

Those are the same thing. In fact, the former is turned into the latter by the engine, but the former is easier to read in most cases. You only need SUMX when doing a specific itteration or the expression has more than 1 field ([units] * [price] for example)