DAX - Changing granularity level for SUMIF calculation

[Anonymous]

Hi everyone,

I am stuck on something in DAX and this is driving me crazy.

Here is my problem:

I have the below data: Team/Snapshot/Over/Current/Total.

I know how to calculate Daily% and RollAvg% in DAX.

But I need to do something else (please refer to 2nd screenshot)

This is the summarized version of the previous table at the Date/Snapshot level.

Now I need to recalculate my measures.

However, when summing up Over and Total, I need to exclude values that have Exclusion Flag = 0 in the previous table.

From there, I can then calculate my Daily %

And THEN, I want can calculate my Rolling Avg which is based on the newly re-calculated Daily%.

There is an important requirement here: we need to calculate the Avg of the Daily %. Meaning that we need to calculate the Daily% FIRST and then average them.

I only need help calculating RollAvg % at the Snapshot Level.

Anyone knows how to do this ?

Thanks in advance,

Jason.

[LivioLanzo]

Hi @Anonymous

are you trying to return a table or doing it in a matrix? can you post a table which can be copy pasted?

[Anonymous]

@LivioLanzo

I am trying to build a Matrix.

TEAM	Snapshot Level	Over	Current	Total	Daily %	RollAvg %	Exclusion Flag
TeamA	2018/12/19	$   1,216,614.00	$         432,697.00	$     1,649,311.00	73.76%	73.76%	1
TeamA	2018/12/20	$   1,276,536.00	$         482,463.00	$     1,758,999.00	72.57%	73.17%	0
TeamA	2018/12/21	$   1,296,174.00	$         434,994.00	$     1,731,168.00	74.87%	73.74%	1
TeamA	2018/12/22	$   1,264,147.00	$         468,026.00	$     1,732,173.00	72.98%	73.55%	1
TeamA	2018/12/23	$   1,243,103.00	$         419,726.00	$     1,662,829.00	74.76%	73.79%	1
TeamB	2018/12/19	$       410,890.00	$   19,124,489.00	$   19,535,379.00	2.10%	2.10%	1
TeamB	2018/12/20	$       498,982.00	$   18,379,548.00	$   18,878,530.00	2.64%	2.37%	1
TeamB	2018/12/21	$       474,621.00	$   17,911,327.00	$   18,385,948.00	2.58%	2.44%	0
TeamB	2018/12/22	$       425,197.00	$   19,423,492.00	$   19,848,689.00	2.14%	2.37%	1
TeamB	2018/12/23	$       492,982.00	$   18,855,618.00	$   19,348,600.00	2.55%	2.40%	1
TeamC	2018/12/19	$           1,798.00	$               713.00	$               2,511.00	71.60%	71.60%	1
TeamC	2018/12/20	$         (1,152.00)	$               1,818.00	$               666.00	-172.97%	-50.68%	1
TeamC	2018/12/21	$         (4,152.00)	$             (565.00)	$           (4,717.00)	88.02%	-4.45%	1
TeamC	2018/12/22	$         (2,991.00)	$             (841.00)	$           (3,832.00)	78.05%	16.18%	1
TeamC	2018/12/23	$             694.00	$           (1,455.00)	$             (761.00)	-91.20%	-5.30%	0
TeamD	2018/12/19	$   2,295,400.00	$   15,720,276.00	$   18,015,676.00	12.74%	12.74%	1
TeamD	2018/12/20	$   2,009,030.00	$   15,171,769.00	$   17,180,799.00	11.69%	12.22%	1
TeamD	2018/12/21	$   2,126,146.00	$   15,303,235.00	$   17,429,381.00	12.20%	12.21%	1
TeamD	2018/12/22	$   2,376,371.00	$   15,322,171.00	$   17,698,542.00	13.43%	12.52%	1
TeamD	2018/12/23	$   2,298,318.00	$   15,011,664.00	$   17,309,982.00	13.28%	12.67%	1
TeamE	2018/12/19	$   2,000,695.00	$   30,082,221.00	$   32,082,916.00	6.24%	6.24%	1
TeamE	2018/12/20	$   2,075,585.00	$   30,408,035.00	$   32,483,620.00	6.39%	6.31%	1
TeamE	2018/12/21	$   2,163,131.00	$   30,944,277.00	$   33,107,408.00	6.53%	6.39%	1
TeamE	2018/12/22	$   2,010,620.00	$   30,501,306.00	$   32,511,926.00	6.18%	6.34%	0
TeamE	2018/12/23	$   2,264,367.00	$   29,956,934.00	$   32,221,301.00	7.03%	6.47%	1
TeamF	2018/12/19	$       156,585.00	$         731,440.00	$         888,025.00	17.63%	17.63%	1
TeamF	2018/12/20	$     (123,253.00)	$         854,353.00	$         731,100.00	-16.86%	0.39%	1
TeamF	2018/12/21	$       (20,914.00)	$         863,498.00	$         842,584.00	-2.48%	-0.57%	1
TeamF	2018/12/22	$       (83,613.00)	$         795,776.00	$         712,163.00	-11.74%	-3.36%	0
TeamF	2018/12/23	$     (110,426.00)	$         875,583.00	$         765,157.00	-14.43%	-5.58%	0

[LivioLanzo · Accepted solution]

Hello @Anonymous

check out the file I've uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1arOlNdFWZE0Yit

let me know if this works for you

[Anonymous]

@LivioLanzo This is amazing !

But why do we have to create a calendar table for this ?

[LivioLanzo]

Hi @Anonymous, it is good practice to use a Date table in your model when you have a date column in the fact table

[Anonymous]

Hi @LivioLanzo,

Just ignore this post !

Still testing. Will keep you posted.

Jason