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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.