Need only to use one date dimension in this measure, how to do that (Sample file included)

[Bokazoit]

This measure is created using a detached Date dimension - as can be seen from this data model:

Doing that makes it possible to calculate the correct distinct count, using this DAX:

Indtægter verserende sager = 

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter &&
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)

The VAR MinDato gives the  minimum date in my date filter:

But the datamodel is not that userfriendly, and I would like to use the date from my Date dimension (DimDato) instead of the detached date dimension. 

The problem is that when I use the Date dimension it gives me the wrong result. If I use this measure:

Antal verserende sager = 

VAR MinDato = MIN(DimDato[DatoKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter && 
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)

Were I use the Date Dimension (DimDato) instead of the detached date dimension, I get 4 records more:

The minimum date is 1. april 2022:

The green row is correct since it is before the minimum date and the filter in the measure works, but the 4 red rows is not correct as can be seen - since all 4 records is after 1. april 2022.

To me it looks as if the variable MinDato (= minimum date from the datefilter) is calcualted differently than I would expect.

Below is sample file attached:

https://drive.google.com/file/d/1yjp20i4tZP0GAnSbSe-06f4TtkQowIrh/view?usp=sharing 

There is two sheets, and the results in each sheet is correct, but for the result in the sheet 'Forecast' to be correct, I need to create a new date dimension as described above. I would like to use just the Primary date dimension.

Hope this gives You what You need.

Thx in advance 

 🙂

[Payeras_BI]

Hi @Bokazoit ,
Let's see if now we got what you wanted when introducing the variable "FirstDayQuarter" in your latest version.

[Bokazoit]

What if I wants to show it with other filters like month etc. should I add that too?

[Kumail]

Hello @Bokazoit 

The reference image and the file are attached.

https://drive.google.com/file/d/1YABbpnVlDchNNU-bCnI6TvizFioz0U3D/view?usp=sharing

Hope this helps.

Regards

Kumail Raza

LinkedIn: https://www.linkedin.com/in/kumail-raza-76508856/

[Bokazoit]

Sorry, nope that did ot change anything. 

[Kumail]

@Bokazoit 

In the updated file, the new measure (

2Antal verserende sager) is updated to refer DimDato table and the model updated with DimDato connected to FilterDato to get the report working with DimDato[Dato] in filter visual.

Regards

Kumail Raza

LinkdedIn: https://www.linkedin.com/in/kumail-raza-76508856/

[Bokazoit]

When I choose may the result looks like this:

But should look like this:

According to a MS moderator its because the 7 missing in April is because of the filter when using the measure (be aware it is altered a bit):

Antal verserende sager = 

VAR MinDato = MIN(DimDato[DatoKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter && 
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)

 As my respons were, it was much easier using report manager.

[Payeras_BI]

Hi again @Bokazoit,

Find the pbix file attached where you will find a duplicate of your Forecast tab where I am using DimDato to slice as per your requirement and not FilterDato.

As per my original post I only changed the MinDato variable.

You will need to adapt the rest of your measures, instead of this:

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])

use this:

MinDato = CALCULATE(MIN(DimDato[Dato]),ALLSELECTED(DimDato))

I hope it helps. 

[Payeras_BI]

Hi @Bokazoit ,

This might work:

TRY THIS = 
VAR MinDato = CALCULATE(MIN(DimDato[Dato]),ALLSELECTED(DimDato))
RETURN
CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= MinDato && 
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)

[Bokazoit]

The measure is changed, but I do not see how that would alter anything. but Your idea does not give the expected result:

Indtægter verserende sager = 

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    SUM(FactArvesager[#forventetarv]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter &&
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)