Help adding holidays to measure

[Anonymous]

Hello BI community,  

I am currently working on a measure that automatically calculates the variance of 2 days worth of financial data. So far it works but when a holiday comes along the measure does not respond. I added a holiday table and created a relationship between both tables but that does not work. Is there anything that i can add or do to make the table recognize the holidays. This is my current measure; 

Variance=  VAR __DAYB4YEST = 
IF(WEEKDAY(TODAY())=2,SUMX(FILTER(GLTABLE,GLTABLE[DATE]=TODAY()-4),GLTABLE[BALANCE]),SUMX(FILTER(GLTABLE,GLTABLE[DATE]=TODAY()-2),GLTABLE[BALANCE]))

VAR __YEST = IF(WEEKDAY(TODAY())=2,SUMX(FILTER(GLTABLE,GLTABLE[DATE]=TODAY()-3),GLTABLE[BALANCE]),SUMX(FILTER(GLTABLE,GLTABLE[DATE]=TODAY()-1),GLTABLE[BALANCE]))

Return (__YEST-__DAYB4YEST)

Desired OutcomeSample Data

Will appreciate any help. Thank you in advance.

[v-yulgu-msft]

Hi @Anonymous ,

---

Is there anything that i can add or do to make the table recognize the holidays. 

---

How to recognize? Should holiday dates be shown in visual? If yes, how to calculate balance and variance for holiday? Are holiday dates listed in fact data table and their corresponding balance are empty? Or holiday dates will be skipped in fact data table? 

Please provide more sample data containing holidays.

Regards,

Yuliana Gu

[Anonymous]

I need the Holiday dates to be skipped sorry i forgot to mention that. I have a seperate table with the holidays after creating a relationship between the tables i thought just filtering would work but unfortunately it did not. 

This is the holiday table i am using