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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
haloboyscp
Helper I
Helper I

Get a result for the values inbetween the matching dates (From Different Tables)

Hello,

 

So I've got a penetration rate that changes only when new houses are added at specific dates.

So for example at 1st January I have 100.000 houses, but 10.000 houses are added on the 1st of March.

Is there a way the model keeps the 100.000 for January and February and only adds in March.

 

Ill leave below the set for the two tables:

Table 1: Nr of total houses for each date when Houses are added

haloboyscp_0-1619624518807.png

 

Table 2: Multiple dates for different data and for which I need to summarize the total number of houses which depends on date from table 1. 

haloboyscp_1-1619624570974.png

 

I dont know if i use the right words, but i am looking forward to hear from you.

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@haloboyscp , Do you need running total or cumulative

example measure

 

Cumm Sales = CALCULATE(SUM(Table[houses ]),filter(allselected(Table),Table[Date] <=max(Table[Date])))

 

with date tbale

Cumm Sales = CALCULATE(SUM(Table[houses ]),filter(allselected(Date),Date[Date] <=max(Date[Date])))

View solution in original post

Hey,

 

Thanks.

 

I need the cumulative. I am not sure though your solution answers the question.

 

So I would say what may be missing is the following:

On table 2 a column must be added for summing the houses from table 1 (on table 1 houses are added for each new date). Table 2 have several days for a month, on which the new column will be added that match the houses for the specific dates.

 

I hope I am clear on what I am saying. 

But imagine a CalendarTable, and i'd need those houses to be added as a column according to the date on calendar table.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@haloboyscp , Do you need running total or cumulative

example measure

 

Cumm Sales = CALCULATE(SUM(Table[houses ]),filter(allselected(Table),Table[Date] <=max(Table[Date])))

 

with date tbale

Cumm Sales = CALCULATE(SUM(Table[houses ]),filter(allselected(Date),Date[Date] <=max(Date[Date])))

My friend, I made just some changes and this has worked for me. thanks.

Hey,

 

Thanks.

 

I need the cumulative. I am not sure though your solution answers the question.

 

So I would say what may be missing is the following:

On table 2 a column must be added for summing the houses from table 1 (on table 1 houses are added for each new date). Table 2 have several days for a month, on which the new column will be added that match the houses for the specific dates.

 

I hope I am clear on what I am saying. 

But imagine a CalendarTable, and i'd need those houses to be added as a column according to the date on calendar table.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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