cancel
Showing results for
Did you mean:
Highlighted
Member

## Month sum of measure calculated on Date level

Hi,

I'm trying to create measures that will handle filtering on a date and time level for the start end end date.
For this I'm using a normal date slicer, and then filtering start and end time based on the min/max dates in the selected range, using two seperate tables as input variables for the start and end time.

Currently I have the measure listed below, which works as intended, but I need to figure out how to get the correct total, when not filtering on a date level.

I get that the measure returns blank when not filtering on a Date level due to the 'HASONEVALUE', but I'm using the 'HASONEVALUE' to return a single scalar value into the following if statements.

How do I feed the if statements with a single value, but get the correct total value shown at a year or month level?

```Sales Revenue =
VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxTime = CALCULATE(MAX('Time min filter'[Time]))
VAR MAXTimeMaxDato = CALCULATE(MAX('Time max filter'[Time]))
RETURN
IF( HASONEVALUE(Periode[Dato]);
IF(VALUES(Periode[Dato]) = MinDato;
CALCULATE(Sales[Sales Revenue];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTime));
IF(VALUES(Periode[Dato]) = MaxDato;
CALCULATE(Sales[Sales Revenue];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato));
Sales[Sales Revenue]));
BLANK())```

Note:
Measure is mainly in danish so:
Dato = Date
Periode = Date table

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Month sum of measure calculated on Date level

Cracked it by more googling and turning to switch for help and referencing another table

Final measure:

```Salg Oms Switch Filter =
VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxTidspunkt = CALCULATE(MAX('Tidspunkt min filter'[Time]))
VAR MAXTidspunktMaxDato = CALCULATE(MAX('Tidspunkt max filter'[Time]))
RETURN
DIVIDE(
SUMX(Salg;
SWITCH( TRUE();
Salg[Bogføringsdato] = MinDato;CALCULATE(Salg[Salg Omsætning];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTidspunkt));
Salg[Bogføringsdato] = MaxDato;
CALCULATE(Salg[Salg Omsætning];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato));
Salg[Salg Omsætning]))
;1000)```
Member

## Re: Month sum of measure calculated on Date level

Cracked it by more googling and turning to switch for help and referencing another table

Final measure:

```Salg Oms Switch Filter =
VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato]))
VAR MaxTidspunkt = CALCULATE(MAX('Tidspunkt min filter'[Time]))
VAR MAXTidspunktMaxDato = CALCULATE(MAX('Tidspunkt max filter'[Time]))
RETURN
DIVIDE(
SUMX(Salg;
SWITCH( TRUE();
Salg[Bogføringsdato] = MinDato;CALCULATE(Salg[Salg Omsætning];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTidspunkt));
Salg[Bogføringsdato] = MaxDato;
CALCULATE(Salg[Salg Omsætning];
FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato));
Salg[Salg Omsætning]))
;1000)```