PLEASE HELP ::: If holiday in date copy previous day data for that or give alternative solution

[Anonymous]

Date	entity	sublog	asset cr
07-12-2021	a	x	1
07-12-2021	a	y	10
07-12-2021	a	z	10
08-12-2021	a	x	2
08-12-2021	a	y	4
08-12-2021	a	z	1
09-12-2021	a	x	3
07-12-2021	b	p	2
07-12-2021	b	q	2
07-12-2021	b	r	2
08-12-2021	b	p	3
08-12-2021	b	q	3
08-12-2021	b	r	3
09-12-2021	b	p	4
07-12-2021	b	q	4
07-12-2021	c	xx	3
07-12-2021	c	yy	3
08-12-2021	c	zz	3
08-12-2021	c	xx	4
08-12-2021	c	yy	4
09-12-2021	c	zz	4
07-12-2021	a	z	-10
07-12-2021	b	q	3
07-12-2021	c	xx	4
08-12-2021	a	y	5
08-12-2021	b	r	6
08-12-2021	c	zz	7
09-12-2021	a	x	1
09-12-2021	a	z	0
12-12-2021	a	x	1
12-12-2021	a	y	10
12-12-2021	a	z	0
14-12-2021	a	x	2
14-12-2021	a	y	4
14-12-2021	a	z	10
15-12-2021	a	x	3
12-12-2021	b	p	2
12-12-2021	b	q	2
12-12-2021	b	r	2
14-12-2021	b	p	3
14-12-2021	b	q	3
14-12-2021	b	r	3
15-12-2021	b	p	4
15-12-2021	b	q	4
12-12-2021	c	xx	3
12-12-2021	c	yy	3
12-12-2021	c	zz	3
14-12-2021	c	xx	4
14-12-2021	c	yy	4
14-12-2021	c	zz	4
14-12-2021	a	z	-10
15-12-2021	b	q	3
12-12-2021	c	xx	4
12-12-2021	a	y	5
12-12-2021	b	r	6
14-12-2021	c	zz	7
14-12-2021	a	x	1
14-12-2021	a	z	0

Please refer above table as source data. called SHEET 1. 

And another source which give me holiday information below,

Day	Holiday
07-12-2021	FALSE
08-12-2021	FALSE
09-12-2021	FALSE
10-12-2021	TRUE
11-12-2021	TRUE
12-12-2021	FALSE
13-12-2021	TRUE
14-12-2021	FALSE
15-12-2021	FALSE

So basically from both table you got that whenever Holiday = TRUE , There are no data in SHEET1. so i wanted previous day data countabily over there.

Reason why i required that:

Refer my below measures:

avg asset =

SUMX (

SUMMARIZE (

Sheet1,

Sheet1[entity],

Sheet1[sublog],

"Min Asset Value",

AVERAGEX(

SUMMARIZE (

Sheet1,

Sheet1[Date].[Date],

Sheet1[entity],

Sheet1[sublog],

"Asset_cr", SUMX ( Sheet1, Sheet1[asset cr] )

),

[Asset_cr]

)

),

[Min Asset Value]

)

Min_ asset =

SUMX (

SUMMARIZE (

Sheet1,

Sheet1[entity],

Sheet1[sublog],

"Min Asset Value",

MINX (

Filter(

SUMMARIZE (

Sheet1,

Sheet1[Date].[Date],

Sheet1[entity],

Sheet1[sublog],

"Asset_cr", SUMX ( Sheet1, Sheet1[asset cr] )

),

[Asset_cr] <> 0

),

[Asset_cr]

)

),

[Min Asset Value]

)

I need holiday dates also in visulisation with previous day data.

Required Output:

 in power bi.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

Create measures.

Measure 2 = CALCULATE(SUMX(Sheet1,Sheet1[asset cr]),FILTER(ALL('holiday information'),'holiday information'[Day]=MAXX(FILTER(ALL('holiday information'),'holiday information'[Day]<=MAX('holiday information'[Day])&&'holiday information'[Holiday]="FALSE"),'holiday information'[Day])))

Measure 3 = CALCULATE(AVERAGEX(Sheet1,Sheet1[asset cr]),FILTER(ALL('holiday information'),'holiday information'[Day]=MAXX(FILTER(ALL('holiday information'),'holiday information'[Day]<=MAX('holiday information'[Day])&&'holiday information'[Holiday]="FALSE"),'holiday information'[Day])))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak  OP sir.

but requirment is not 100% correct. I feel its because of i couldnot explain properly.

Currently from your solution,

new measure22 =

calculate

(SUMX

( Sheet1, Sheet1[asset cr] ) , filter(ALL('Holiday data'),'Holiday data'[Day] = maxx(FILTER(ALL('Holiday data'),'Holiday data'[Day] <= MAX('Holiday data'[Day]) && 'Holiday data'[Holiday] = FALSE()),'Holiday data'[Day])))

is given wrong average at su category level and I want sum of all subcategory as category value.

You can refer my min asset formula,

Min_ asset =

SUMX (

SUMMARIZE (

Sheet1,

Sheet1[entity],

Sheet1[sublog],

"Min Asset Value",

MINX (

Filter(

SUMMARIZE (

Sheet1,

'Holiday data'[Day],

Sheet1[entity],

Sheet1[sublog],

"Asset_cr", SUMX ( Sheet1, Sheet1[asset cr] )

),

[Asset_cr] <> 0

),

[Asset_cr]

)

),

[Min Asset Value]

)

Thank you so much in advance.

[Anonymous]

@amitchandak @AlexisOlson @Greg_Deckler 

Kindly help masters pls

[amitchandak]

@Anonymous , Make sure this holiday flag is part of date table marked as date table and then try measure like

new measure =
calculate(SUMX ( Sheet1, Sheet1[asset cr] ) , filter(all('Date'), 'Date'[Date] = maxx(filter(all('Date'),'Date'[Date]<= max('Date'[Date]) && 'Date'[Holiday] =False()), 'Date'[Date])))

Use date from date table in visual