Forum Discussion
Time Measure Issue (Chart x Table)
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.
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
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],",")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:
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" = 4Type Date YTD_Value Month_Value A 01/11/2019 0 0 B 01/11/2019 0 0 A 01/12/2019 1 1 B 01/12/2019 1 1 A 01/01/2020 4 4 B 01/01/2020 2 2 A 01/02/2020 3 -1 B 01/02/2020 3 1
Anyone?