Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX

Hi,

Please help me with the  DAX formula for the below problem to work dynamically.

  In my data, there is 2 calculation 1. Performance and  2. Timing. I have to calculate this with the conditions below.

----->     The performance effect should be assessed every month on the same number of days & days similar in nature(a Monday being compared to a Monday, and a Saturday to a Saturday). Any extra day is considered as Calendar effect.

This calculation is applied consistently to all POS included in the Performance effect.

Example: The month of June 2015 started on a Friday and ended on a Saturday, whereas the month of June 2014 started on a Thursday and ended on a Friday. Consequently, both the 1st Thursday of June 2014 and the last Saturday of June 2015 are considered as Timing effect  this calculation is done at once for all POS included in the Performance effect.

 

 

 

 

The Performance effect should be assessed every month on POS that have a comparable period of activity. POS that undergoduring the month a period of inactivity (due to repairs, renovation, etc. whatever the reason) that is considered significant (= at least 6 days of activity lost) are excluded from the Performance effect and included in the Timing effect. (This applies to the full month of Revenue of the POS, not only to the days of inactivity )

Partial days of activity are regarded as full days of activity.

Bank holidays (regional or national) are not considered as days of activity lost, and are not factored as Timing effect. Should bank holidays create a bias in the analysis of the Performance effect of the month, it will simply becommented next to the figures.

 

I have used the dax formula, List =
var cn = YEAR(TODAY())
var py = cn-1
Return
SWITCH(INT('table1'[Year]),
py, if('Table 1'[year]=STARTOFMONTH('Table 1'[date]),"T","P"),
cn, if('Table 1'[year]=ENDOFMONTH('table 1'[date]),"T","P")
)

But its not working with renovation period as well as for the leap year. How can I acheive this? Please help me with exact formula.

TIA

Need Help

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Did you have a CALENDAR table? How about changing the formula as below?

     

    var cn = YEAR(TODAY())
var py = cn-1
Return
SWITCH(INT('table1'[Year]),
py, if('Table 1'[year]=STARTOFMONTH('CALENDAR'[date]),"T","P"),
cn, if('Table 1'[year]=ENDOFMONTH('CALENDAR'[date]),"T","P")
)

    BTW, Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Hi,
      The solution I have mentioned works well for normal year, whereas in leap year from February there are two dates as T (last days of month) and 2 dates as P (first two days of the month).
       
      I would like to request support to write DAX as follow,
      1. First, condition to check leap year or not.
      2. If normal year, run the existing DAX code.
      3. If leap year, run the new DAX code (which I dont have)
       
      Can you help me with DAX as above? Please revert for any clarifications.