Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Quarter Days data

Hi All,   I have a visual in my report where I'm showing data Q/Q like below.     What bussiness wants is that I should show the exact same day's data for each quarter for example today is...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks for the response but the calculation will not solve the issue. For example tommorrow will be 1st of March and that will be 29th Day of my quarter but the variable will pick the day as 1 and the result will calculate the value based on the day = 1 for tommorrow. However it should be like 29th Day of the quarter.

andhiii079845's avatar
andhiii079845
Icon for Solution Sage rankSolution Sage
3 years ago

Your problem is only to get for the 1st of March (if this day is today) the value (fiscal_day_of_quarter field) 29 from the Dimtable? Is this right? The both tables are in a relationship?
You can try like this as a example:

DimTable[Day] is your fiscal_day_of_quarter column in the DateTable. 

Fact is your second table. 

 

Measure = 
Var currentday = TODAY()
Var day = CALCULATE(VALUES(DimTable[Day]),FILTER(RELATEDTABLE('Fact'),'Fact'[Date]=currentday))

RETURN Day

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I tried the below DAX but its not working

     

    Measure =
    Var currentday = [Today]
    Var day = CALCULATE(VALUES(D_DATE[FISCAL_DAY_OF_QUARTER]),FILTER(RELATEDTABLE(F_ACV),F_ACV[CLOSE_DATE]=currentday))

    RETURN
    CALCULATE([ACV],D_DATE[FISCAL_DAY_OF_QUARTER] = day)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I'm using the below calculation for the time being to get the desired result. the issue with this DAX is I'll have to add the previous months days to the calculation like tommorow I'll replace 0 with 28 in my calculation.

       

       

    • andhiii079845's avatar
      andhiii079845
      Icon for Solution Sage rankSolution Sage
      3 years ago

      WHY [TODAY] instead of TODAY() ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I'm using date direct from my DB.