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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ninakarsa
Helper II
Helper II

calculating sales between dates

Hi Members

 

I am new to powerbi and need a bit of help.

 

So I have 2 tables, table 1 has the budget which needs to be met between from date and to date by car park. Table 2, shows the sales made by date for the car park. Based on these 2 tables I need to create table 3 which shows the total sales against the budget by car park.

 

Table 1:

Car Park Budget FROM DATETO DATE
Area A                               10,000.0001/05/202031/10/2020
Area B                             200,000.0001/01/202031/12/2020

 

Table 2:

Car Park Date Amount
Area A01/05/2020            1,000
Area A10/05/2020            1,500
Area A10/06/2020            2,000
Area A15/06/2020              150
Area B10/06/2020            1,000
Area B10/07/2020        500,000
Area B11/07/2019          60,000
Area B05/08/2020          10,000
Area B01/02/2020          50,000

 

Table 3:

Car Park Budget FROM DATETO DATETotal 
Area A                               10,000.0001/05/202031/10/2020       4,650 
Area B                             200,000.0001/01/202031/12/2020  561,000 

 

1 REPLY 1
az38
Community Champion
Community Champion

Hi @ninakarsa 

try to add a measure in your table 1:

 

Total = CALCULATE(
    SUM(Table2[Amount]),
DATESBETWEEN(Table2[Date],SELECTEDVALUE(Table1[FROM DATE]),SELECTEDVALUE(Table1[TO DATE]))
)

 

before, be sure you have a relationships between Table1 and Table2 by [Car Park] columns

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors