Forum Discussion
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 but dosent work on table, can anyone help me?
Period_Value =
IF(MONTH(SELECTEDVALUE('Calendar'[Date]))=1,
SUM(Planilha0[YTD_Value]),
SUM(Planilha0[YTD_Value]) - CALCULATE(SUM(Planilha0[YTD_Value]),PREVIOUSMONTH('Calendar'[Date]))
)
13 Replies
Anonymous - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Anonymous I would maybe check to ensure the table is using the Calendar fields, and it may need that [Date] field as well. Did you build the table separately from the chart? or did you copy the chart, then change it to a table visualization?
Anonymous - Maybe try MAX('Calendar'[Date]) instead of SELECTEDVALUE
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: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 4 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 )
instead i got this:
.Pbix filePeriod_Value = IF(MONTH(Max('Calendar'[Date]))=1, SUM(Planilha0[YTD_Value]), SUM(Planilha0[YTD_Value]) - CALCULATE(SUM(Planilha0[YTD_Value]),PREVIOUSMONTH('Calendar'[Date])) )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.
