Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
KSYP11
New Member

IF statement to combine data from two different tables does not sum results correctly

Hello, 

 

I am working with 4 tables. The context is I'm trying to create a measure that takes values from two different tables if a certain condition is met. 

 

Date Table 

Project Table - has all the ProjectIDs

Actuals Table - actual amount of charges from the start of the FY to date

Assignments Table - what has been assigned from the start of the FY to date. 

Here is the relationship of the tables

KSYP11_4-1648067151681.png

 

Measure 1: Pay period over = IF(TODAY()>('Date Table'[Start of Month]),1,0) 

               - In this measure I'm trying to indicate what pay periods have elasped so I know if I should use the actual amounts or the                       assigned ones. 


Measure 2: Actuals+Assignments=IF(Pay Period Over=1, A_actuals,B_assignments )

                - Both A_actuals and B_assignments are measures as well. 

               - The goal here is to replace any pay periods that haven't occured yet with the assignments that will happen in the future                    and replace all previous assignments with actuals. 

Both of these measures work but they do not summarize correctly. I only get the total for the actuals to date instead of the new measure that combines them both. I need to make a running total for the second measure to plot it, but I can't figure out what will work. (I have tried hasonefilter, hasonvalue but it comes to a point I don't know how to integrate the some of my pre-existing measures.) 

I.e. I've tried this measure with no success:

Actuals+Assignemts_Version2=

IF(HASONEFILTER('Project Table'[Project Task]),IF([Pay Period Over]=1,[A_actuals],[B_assignments]),
SUMX(FILTER(Assignments,[A_actuals]),[B_assignments])
 

Below are examples of my data. The bottom right table with the running total is what I'm ultimately trying to achieve. 

KSYP11_5-1648067852114.png


Any help or guidance would be greatly appreciated! 

 

2 REPLIES 2
KSYP11
New Member

Hello! I appreciate your reply, unfortunately that measure isn't working because the PayPeriod measure comes from the "Date" table and not the Project table and it is not recognizing it as an option. 

amitchandak
Super User
Super User

@KSYP11 , Based on what I got

 

assuming both are measure 

if(isbalnk([A_actuals]),[B_assignments],[A_actuals])

 

M1 = sumx(summarize(Table, Table [project], Table[Payperiod], "_1", if(isbalnk([A_actuals]),[B_assignments],[A_actuals])  ) , [_1])

 

 

Cumm =calculate([M1], filter(allselected(Table), Table [project] =max(Table [project]) && Table[Payperiod] <= max(Table[Payperiod])))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.