Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Time Measure Issue (Chart x Table)

Hi,  I do have a table with "YTD_values", and i use the following measure to get the "Period_Values" per month with a date condition on january (fiscal year), thats work's great on line chart bu...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous - Maybe try MAX('Calendar'[Date]) instead of SELECTEDVALUE

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    we are almost there!

    That worked until i replace the table row's item from "Date" to "Type" , lets suppose i do have this table:


    TypeDateYTD_Value
    A01/11/2019  0
    B01/11/2019  0
    A01/12/2019  1
    B01/12/2019  1
    A01/01/2020  4
    B01/01/2020  2
    A01/02/2020  3
    B01/02/2020  3


    So i expect that the "Period Value" of type "A" from 01/12/2010 to 01/02/2020 = 4 (+1 of dec + 4 of jan - 1 of feb )

    instead i got this:



    .Pbix file  


     

     

     

    Period_Value = 
IF(MONTH(Max('Calendar'[Date]))=1,
    SUM(Planilha0[YTD_Value]),
    SUM(Planilha0[YTD_Value]) - CALCULATE(SUM(Planilha0[YTD_Value]),PREVIOUSMONTH('Calendar'[Date]))
)

     

     

     



    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Anonymous - Wait, I'm confused. You took date away so what is the previous month in that context? Are you saying that the current month when you only have Type is the latest (max) month in the data and then previous month is 1 month before that?

       

      I'm still not down with using PREVIOUSMONTH, it is a tricky little black box. If the above are your requirements, I would do something like:

       

      VAR __Date = MAX([Date])

      VAR __Previous = EOMONTH(__Date,-1)

      VAR __PreviousYear = YEAR(__Previous)

      VAR __PreviousMonth = MONTH(__Previous)

      RETURN SUMX(FILTER(ALL('Table'),YEAR([Date])=__PreviousYear && MONTH([Date])=__PreviousMonth),[Column])

       

      Then you know exactly what you are getting and how you got there versus relying on the tricky little black box that are time "intelligence" functions.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Sorry if i was not clear, the code didnt work out yet.

        I just want Period of values from a YTD table but i have to inform a condition based on month to PBI do a diferent calculation on january(start of my fiscal year), the PBI became crazy when i do select "January" + other month for some reason :(.


        The code that you shared with me:

        Period_Value_vr2 = 
VAR __Date = MAX([Date])
VAR __Previous = EOMONTH(__Date,-1)
VAR __PreviousYear = YEAR(__Previous)
VAR __PreviousMonth = MONTH(__Previous)
RETURN 
SUMX(FILTER(ALL('Planilha0'),YEAR([Date])=__PreviousYear && MONTH([Date])=__PreviousMonth),Planilha0[YTD_Value])


        Image showing the result with my desired result:



        File To test:
        PBIX File to test