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


Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous - Here is what I do not understand. You state:

 

Type Date YTD_Value
A 01/11/2019 0
B 01/11/2019 0
A 01/12/2019 1
B 01/12/2019 1
A 01/01/2020 2
A 01/01/2020 2
B 01/01/2020 2
A 01/02/2020 3
B 01/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 )

 

I get the 1 for december. I get the 4 in january. What I can't fathom is the -1 for February. Seems like it should be +3 or at least +1 so what am I missing?

 

Here is another version for troubleshooting:

Measure 11 = 
    VAR __Date = MAX('Table (11)'[Date])
    VAR __Previous = EOMONTH(__Date,-2)
    VAR __PreviousFinal = DATE(YEAR(__Previous),MONTH(__Previous),1)
RETURN 
    CONCATENATEX(FILTER('Table (11)',[Date]>=__PreviousFinal),[YTD_Value],",")
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    December = 1 (YTD from december ) - 0 (From november) = 1
    January = 4 (YTD from january) = 4 
    February = 3 (YTD on february) - 4 (from january) = -1

    Dec+jan+Feb = 4

    The code shared didnt work:

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago


      Trying to be more clear, i want Month values from a YTD table that reset in 31/december, knowing that, i need to especify a condition on january.

      I need a measure that that show corrects sum of results on my table with/without "date filter"

      I want this "Month_Value" column/measure so i could get total "Month_Value" of  "Type A" from "dec/19 to fev/20" = 4


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


      Anyone?

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Use the following two measure without using time intelligence function：

         

        Period_Value = VAR a = SUM(Planilha0[YTD_Value]) var b = CALCULATE(SUM(Planilha0[YTD_Value]),FILTER(ALLEXCEPT(Planilha0,Planilha0[Type]), MONTH(Planilha0[Date]) = MONTH(MAX('Calendar'[Date]))-1)) return IF(MONTH(MAX('Calendar'[Date])) =1,a,a-b)

Period_Value_vr2 = SUMX(Planilha0,[Period_Value])

         

        And it will work.

         

         

        For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZ_WK2jq2CRKuXrHxaWCHYMBZGbFdlvsnmnUOcX_x6URqw?e=9el4zL

         

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

         

        Best Regards,

        Dedmon Dai