Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help adding holidays to measure

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.
Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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