cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Barslund Member
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
Barslund Member
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)
1 REPLY 1
Barslund Member
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)