Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
)
@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 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]))
)
@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" = 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?
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
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
anyone?
@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?
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Yes, i created a calendar table, there is a relationship between them too.
Yes, iam using "Calendar Date" on both visual's.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |