Progress of measure in time from 2 tables with non-unique date columns

[spirodro]

Hi everyone,

I would appreciate your help on this one:

I have a measure that sums the values from table 1 and divides this result with the count of rows from table 2.

• Now the problem is that I would like to show the progress of this measure over time.
• Table 1&2 both include a date column with non unique values (meaning that for each date corresponds multiple values/rows )
• My idea was to create a new table with my own unique dates and try for each unique date to
  • look up the sum of values from table 1 corresponding to that date
  • look up the count of rows from table 2 corresponding to that date

So far I haven't been successful to do that. I believe it should be a combination of "CALCULATE() and LOOKUPVALUE" but I had quite many failed attempts do far.

I'm looking forward to your suggestions and/or alternative approaches!

[v-shex-msft · Accepted solution]

Hi @spirodro,

You can use VAR function to get current value, then use it to filter the date.

Sample:

Tables(sheet1, sheet2)

 

Measure(created in sheet1):

Result =
var currDate=MAX(Sheet1[Date])
return
DIVIDE(COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL(Sheet1),Sheet1[Date]=currDate),"CaseNo",[CaseNo]))),
 SUMX(FILTER(ALL(Sheet2),Sheet2[Date_to]=currDate),Sheet2[Value]),0)

Regards,
Xiaoxin Sheng

[CahabaData]

Your date table is joined to the 2 tables.

At the Visual level

Then your Table 1 has a sum at bottom

Table 2 has a count total at bottom

You add the Date table as a slicer - - so when you pick a date the tables display their relative values.

yes?

So the needed calculation is a measure that will be on a card visual?

[BhaveshPatel]

As per my understanding of the issue, PowerQuery would be more suitable for such kind of operations. Can you please post the sampe data and expected output for the deeper understanding of the problem. It would be greatly appreciated.

[spirodro]

Hi again,

Thanks for your replies. I will try to explain better. Below you can see the 2 tables I have loaded in my model.

The measure I'm using is basically  Measure= DISTINCTCOUNT(SynergiDataIncidence[Case_no])/SUM(SynergiManHours[Value])

So I'm calculating something based on the green fields below. These fields also refer to some dates (marked with red). But there are multiple non-unique days for each case_no and Value. E.g:

You will notice that for the same date there are many case_no and values.

Now what I'd like to do is to show the measure over time, which means I want to know in any date what the measure would be. Of course the goal is to be able to show that on a graph. There is no way I can use the existing date columns of course since they are not linked together.

I hope that will give you more insight

[CahabaData]

The two tables individually will aggregate (either sum or count as appropriate) in a visual.  But to get them together into the same visual you need a 3rd Date Table - - and join the Date field to this new table.

You can quickly sanity check this idea by making manually a new table (use the Enter Data feature) and just add 3 or 4 dates that are common to both tables.  Then make the join in the relationship area.

See how that goes.

[spirodro]

Hi Community

I would appreciate your help on that one:

I have a measure that calculates a sum of values from one table (say table1) and divides the result with a count of rows from another table (say table2). The problem is that I would also like to display the progress of this measure in time.

• Each of these tables (table1&2) includes a date-time column but not with unique values (meaning that for the same date you can have multiple values-rows)
• My idea was to create a new table with my own unique dates and try for each day to
  • look up for the sum of values corresponding to that date from table 1
  • look up for the count of rows corresponding to that date from table 2

I still have trouble with it. I guess it's a combination of "CALCULATE() & LOOKUPVALUE()" but I've had a number of not so successful attempts.

Thanks in advance

Looking forward to your recommendations on the right formulas or even different ways to calculate this!

[mike_honey]

I would create a Date table.  The easiest way is using Modeling / New Table, then writing a DAX formula like:

Dates = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))

Then I would use the Relationships window to add relationships between table1 and Dates and then table2 and Dates.

Then you can use the Dates.Date field in your visuals to analyze over time.