Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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
6 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
6 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],",")