Time Measure Issue (Chart x Table)

[Anonymous]

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

[Greg_Deckler]

@Anonymous - Maybe try MAX('Calendar'[Date]) instead of SELECTEDVALUE

[Anonymous]

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 file  

Period_Value = 
IF(MONTH(Max('Calendar'[Date]))=1,
    SUM(Planilha0[YTD_Value]),
    SUM(Planilha0[YTD_Value]) - CALCULATE(SUM(Planilha0[YTD_Value]),PREVIOUSMONTH('Calendar'[Date]))
)

[Greg_Deckler]

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

[Anonymous]

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]

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

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]

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

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

[v-deddai1-msft]

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]

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 

[Anonymous]

anyone?

[DataZoe]

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

Yes, i created a calendar table, there is a relationship between them too.

Yes, iam using "Calendar Date" on both visual's.

[Greg_Deckler]

@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/434008

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