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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi v-deddai1-msft, Thanks for giving your time to help me.

        The code that you shared worked flawless on the "test.pbix" but i couldnt make it work out on "Real_Report", can u help me figure out what iam doing wrong, please?

        I have 2 type's of errors:

        1 - Wrong Calculation when December is selected with other's month's, ex:

        Correct Period_December/2019_Value = -25.013,80
        Correct Period_January/2020_Value = 4.105,60
        Correct Total sum should be = -20.908,20




        2 - Some month Value's are wrong even if i select only them, ex:

        Correct Period_May/2020_Value = 2.638,0




        OBS: Like i said on my past post's, my old measure "Old_Period_Value" do work if i select just 1 month, you can use it as a reference to identify if the values are correct or not, or calculate on the table too.

        Real_DB.pbix