PQE, sum from column (data until latest month) based on list.max from another table/column

[Namoh]

Hi, I'll try to explain what I need.

I have a table which contains all the targets for the coming year for different losses for each facility.

One of our kpi's is measured by a formula based on some of these losses.

We want to show on a monthly basis the months targets and actuals, but also the YTD targets and actuals.

This means that for the targets I need a formula that looks at table A column X to see what the current latest month is and based on this month make a sum of the target values from all months uptill the last month.

Table A

FY		Month (column X)
2020		March
2021		April
2021		May
2021		June

FY		Month		Target Loss
2021		April		5
2021		May		26
2021		June		21
2021		July		8
2021		August		94
2021		September		34
2021		October		4
2021		November		24
2021		December		9
2021		January		57
2021		February		21
2021		March		7

In this example, the current FY is now at June.

I want to calculated the total YTD from the Target Loss column from the other table: 5+26+21=52

But next month it should calculate uptill July making it: 5+26+21+8=60

I want this formula in PQE, not DAX.

Any idea's how to solve this?

[amitchandak]

@Namoh , if you have date, you can use dates dates YTD

Or create a Date like this , and change datatype to date

Date = "01-" & [Month] & "-"& [Year]

Refer video : https://www.youtube.com/watch?v=yPQ9UV37LOU&t=7s //first few min

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[FarhanAhmed]

You can use TotalYTD function to calculate Year To Date Value

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

[Namoh]

I tried with DAX, but I'm getting the same number for all facilities.

This is what I used, did I go wrong somewhere?

YTD_Target = TOTALYTD(SUM(Targets[Target Planned downtime (hrs)]);CALENDAR[Date];ALL('CALENDAR');"3/31")

[Fowmy]

@Namoh 

Try this measure

YTD_Target = 
TOTALYTD(
    SUM(Targets[Target Planned downtime (hrs)]),CALENDAR[Date],,"3/31"
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn 

[Namoh]

Hi.

I've tried 

YTD_Target = TOTALYTD(SUM(Targets[Target Planned downtime (hrs)]);CALENDAR[Date];;"3/31")

but I get the message: argument 3 in the function 'TOTALYTD' is required.

[Fowmy]

@Namoh

Try it this way:

YTD_Target =
 TOTALYTD (
   SUM (Targets [Target Planned downtime (hrs)]);
   CALENDAR [Date] ; 
   "3/31"
 )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, Linkedin

[Namoh]

We're getting closer.

The error is gone, but it now sums up all the values (also the ones in the future) instead of only the currents months into the fiscal year. The FY start at 01-apr, so it only needs to sum the months apr, may, june, july. I thought that was covered by the last part 3/31.?

[Fowmy]

@Namoh 

I tested with my sample data and it works when you apply the ALL(Calendar) FILTER. Please try to replicate the same in your measure

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Namoh]

Hi, did didn't change anything. It still shows the total.

I'm pretty new with PowerBI, but I think (could be completely wrong here) it's because the Targets Table and the Calendar table don't have a direct relationship.

Target is linked to FM, FM is linked to ID, ID is linked to kpi and kpi is linked to calendar. Or doesn't this matter, and should this work?

If so, I have no clue where I'm going wrong.

[Fowmy]

@Namoh 

You should create a relationship for the this measure to work

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Namoh]

At first I couldn't make a relationship because I had a many-to-many relationship.

Then I thought to be smart, and I made a copy of the month-year column and transformed it into the 1st day of the month.

Now I could link the Dates column from Calendar (one) with the 1st Day of the month column of Targets (many).

But now my visual shows nothing.....?

[Namoh]

I found out how to show the numbers in a column via DAX (not that difficult, but they only showed me how to created a new measure).

What I see is, now that I've made the relationship, between the Calendar table and the Targets table that my measure for YTD shows on each row exactly the same figure as the Planned Downtime.

Where in my file does it go wrong?

[Fowmy]

@Namoh 

You may share the PBIX file to check after removing any confidential data.

Thanks

Fowmy

[Namoh]

I would like to share the pbix file but there's so many confidential stuff in there, that it will take me hours to remove it all.

I made some screenshots, to show what I have, hope this might help.

If any other info is needed, please let me know.

The help is much appreciatted.

As you can see, I've created a relationship between Calendar-Date and Target-Begin van de Maand.

I'm trying to do a TotalYTD from column Target Planned downtime (hrs), but I'm getting the same values on each row as the source, see column YTD-Planned_Target.

The fiscal year starts at 01-april, so in the measure I've stated 3/31 as the end of a FY.

To me it looks all ok, but somewhere it goes wrong.

[Namoh]

Any suggestions or info that I can share that will help?

[Fowmy · Accepted solution]

@Namoh 

You need to create a MEASURE with the same formula that I shared earlier. You can add an additional condition to check for blank sales values on dates.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Namoh]

Hi, thanks this helped solving the TOTALYTD sum, the formula that did the trick was: 

IF( SUM(Targets[Target Planned downtime (hrs)]) = BLANK(); BLANK ();TOTALYTD(SUM(Targets[Target Planned downtime (hrs)]);'CALENDAR'[Date];ALL('CALENDAR');"3/31"))

Now I have to find a solution (LIST.MAX?) to show only the TOTALYTD sum uptill the current Date (data is target for whole year so it now shows TOTALYTD uptill the end of the year).

Great help by fowmy!! He knows powerbi and is a great guy for training purposes!

[Fowmy]

@Namoh 

You need to create a date table first the use TOTALYTD(...) function.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Namoh]

@Fowmy , I do have a calendar table in my file.

@FarhanAhmed , thanks for the reply, but that looks like a DAX measure, not something I can use in PQE.

[FarhanAhmed]

May I ask why you need to calculate this in PQE ?

You can take a look into this if you want to calculate YTD in PQE

https://www.mrexcel.com/board/threads/calculate-ytd-values-with-power-query.973390/