Forum Discussion

vijay273162's avatar
vijay273162
Helper III
4 years ago
Solved

Calendar Table help

Hi All,

Attached my Calendar Table for help on creating 1 Calulcated column.

 

https://drive.google.com/file/d/1I9cTfS9Dta6U2SrqBxCbT8Xk7OGGAStH/view?usp=sharing

 

Below is the snapshot of What I need ?

 

Need Help
Show and Tell
Tips and Tricks
  • vijay273162's avatar
    vijay273162
    4 years ago

    Here is the solution. Hpe it helps others !!

     Create a calculated table (only taking the current Quarter Date ) in it.

    Use LOOKUPVALUE syntax, to bring the date into Business Day no. irrespective of Year.

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    vijay273162 Not sure I 100% understand. You want to have the x-axis be the day # of the quarter excluding weekends? Like:

    Day Num of Quarter Column = 
  VAR __Date = [Date]
  VAR __Year = YEAR([Date])
  VAR __Quarter = QUARTER([Date])
  VAR __Table = 
    FILTER(
      ADDCOLUMNS(
        FILTER(ALL('Calendar'),YEAR([Date]) = __Year && QUARTER([Date]) = __Quarter)
        "Weekday",WEEKDAY([Date],2)
      ),
      [Weekday] < 6
    )
RETURN
  COUNTROWS(FILTER(__Table, [Date] <= __Date))
  • vijay273162's avatar
    vijay273162
    Helper III
    4 years ago

    Hi Greg Deckler,

     

    I already have the calculation for Running Day number for current quarter - exluding weekend.

    My request is to replace that day no, taken in x-axis with current-quarter date.

    So, if you download the .pbix file, you can check my "help column" in "calendar table".

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      vijay273162 So like this?

      Current Quarter date = IF(ISBLANK([Running Day - Excluding Wknd.]),BLANK(),[Date])
      • vijay273162's avatar
        vijay273162
        Helper III
        4 years ago

        Hi Greg,

        That's too easy....hahaha...

         

        Here, is the problem, I'm calculating for only Business days.

        So, the 64th day of Q1 FY23 is 4/4/2022 and I need to have the same on 64th day of Q1 FY22.

         

        Snapshot error

         

         

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      vijay273162 Or maybe this?

      Current Quarter date = 
  IF(
    ISBLANK([Running Day - Excluding Wknd.]),
    BLANK(),
    DATE(2022,MONTH([Date]),DAY([Date]))
  )
  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi vijay273162 

    To make it quicker for everyone to help you, could you please fill in each row in the picture with the expected result? Are the expected results based on Running Day - Excluding Wknd.?

     

     

    Best Regards,

    Community Support Team _Tang

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

    • vijay273162's avatar
      vijay273162
      Helper III
      4 years ago

      HI Microsoft Team,

      Thanks for your reply.

      I have found the answer for this problem on my own now.

       

      Kind Request ::

      Help seekers are mostly trying to get some Hints for their problem to find solution.

      Hence, please make a reply ASAP.

       

      I believe, quick response from Support Desk Team - the more customers use this platform for their organization.

       

      Since, Tableau is offering better now-a-days.

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi vijay273162 

        Thanks for your reply. 

        Great to hear that, and may I ask how you solved it? Could you share it? Thanks for your contribution to improve Power BI!

         

         

        Best Regards,

        Community Support Team _Tang

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