cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bokazoit
Post Patron
Post Patron

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

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

 

Bokazoit_0-1652777318231.png

 

 

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:

 

Bokazoit_1-1652777317928.png

 

 

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:

 

Bokazoit_2-1652777318108.png

 

The minimum date is 1. april 2022:

 

Bokazoit_3-1652777317931.png

 

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 

 🙂

 

 

 

9 REPLIES 9
Payeras_BI
Solution Sage
Solution Sage

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

Payeras_BI_1-1652876932501.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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

Kumail
Post Prodigy
Post Prodigy

Hello @Bokazoit 

 

The reference image and the file are attached.

Kumail_0-1652797775301.png

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/

 

Sorry, nope that did ot change anything. 

@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

When I choose may the result looks like this:

 

Bokazoit_0-1653298151921.png

 

But should look like this:

Bokazoit_1-1653298197054.png

 

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
Solution Sage
Solution Sage

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.

Payeras_BI_0-1652792714042.png

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. 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Payeras_BI
Solution Sage
Solution Sage

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])
)
If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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])
)

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors