Carry forward data points from different sources to their respective daily dates

[skithian_]

Good day/evening everyone,

I am relatively new with PowerBI and still learning how to work with DAX. 

The issue I am facing right now is that I have a large set of data that keeps coming in from the field. I have pressure samples measured on different days on different transducers (in this example A and B for simplicity). I want to carry forward my pressure samples to the next days of a month, and then do the same with the next pressure samples for a particular transducer. Please refer to the table visual below on how my data table looks like. Essentially, for each transducer I want to have a stairstepping pressure measurements on a daily basis, and want my dashboard to let me visualize this on different selected transducers. Thus, my data table does not have a continuous dates with blank cells in them, my table only consists of "experiment" data, meaning only the measurements with their sampled dates on different transducers. I tried filldown technique in PowerQuery but it takes forever due to the data size. I don't need the data to be stored really, All I need is to be able to visualize it in BI Dashboard, be it in a quick table or line chart. I appreciate everyone's time and attention on this matter. 

[skithian_ · Accepted solution]

Thank you dearly for your comment. However, it still does not work. I tried something like this from Copilot and ChatGPT, but it wasn't carrying anything forward, it still just looks like a scatter. 

I did make a date table and create one to many relationship from date table to my measurement table dates. When I visualize it in a table and click show all dates with no data and bring in carry forward measure, it still does not show anything, it only matches the measurements with the dates when recording has happened, but the rest are blank or empty. 

[Kedar_Pande]

@skithian_ 

Create a Date Table

DateTable =CALENDAR(MIN('YourDataTable'[Measurement_Date]), MAX('YourDataTable'[Measurement_Date]))

Create a relationship between Measurement_Date in your data table and the Date column in your date table.

New Measure:

CarriedForwardPressure = 
CALCULATE(
LASTNONBLANK('YourDataTable'[Measured_Transducer], 1),
FILTER(
'YourDataTable',
'YourDataTable'[Measurement_Date] <= MAX('DateTable'[Date]) && 
'YourDataTable'[Transducer] = SELECTEDVALUE('YourDataTable'[Transducer])
)
)

Use a line chart or a matrix to visualize the data.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[skithian_ · Accepted solution]

Thank you dearly for your comment. However, it still does not work. I tried something like this from Copilot and ChatGPT, but it wasn't carrying anything forward, it still just looks like a scatter. 

I did make a date table and create one to many relationship from date table to my measurement table dates. When I visualize it in a table and click show all dates with no data and bring in carry forward measure, it still does not show anything, it only matches the measurements with the dates when recording has happened, but the rest are blank or empty. 

[Rupak_bi · Accepted solution]

Hi @skithian_ ,

Here is your solution,

Hope Image explains the way. Please dont create any relationship between date table and the fact table.

[skithian_]

I have a followup question, would it be hard to have this measure to sum up the elements that are being carried forward by date ? Apparantly, PowerBI can't sum them on its own if it is a measure ?

I tried creating a calculated column using your formula, but it does not work, but for measure it works perfectly fine. 

THANK YOU FOR YOUR TIME!

[Rupak_bi]

Hi ,

Measure can sum also. In the last line of the DAX , modify it by replacing max with sum. 

This will work in a calculated column as well with some minor tweaks. Let know your specific ask on this further.

[skithian_]

I tried to respond but fgor some reason it did not record.

Essentially, I tried replacing with SUM but it still just carries forward. 

I want to have a stacked plot as well essentially based on my transducer selection, because I have thousands of them in the field. 

If you look at your plot, I essentially want to have another measure that will sum measured transducer based on transducer selection on each date. Hope this makes sense ?

[Rupak_bi]

Hi @skithian_ 
Here is your solution. This time I created a summarized table with values in column (As you asked earlier) using a very simple two line DAX which solves all your ask. please refer below image. the stack bars are replesenting each transducer and the line is for sum of all on that day. Please note all the axis and values to be taken from the new summarixe table and even slicers as well. dont use other two main table. let me know If this solves your purpose.

[skithian_]

So, I tried this but for some reason it is not working as it supposed to be. I don't have just two transducers, I have like thousands of them. I want to add up values on each date, for a set of selected transducers. Your method somewhat works, but I tried adding up in Excel by hand and I get different results. 

[Rupak_bi]

This should work. Please ensure there should not be any relationship between tables. Share some more sample data. Will check why it's not working.

[skithian_]

Yes I orginally tried making another measure and simply replacing the MAX function with SUM, but it still just carries forward for each individual transducer. So in essence I want the following,

Have a measure that does carrying forward, which you already helped me out with tremendously. Second, I want another measure that will based on the previous measure sum the measured transducer values that are present on the same date even if it is a crried forward value. I am trying to make a "convolution" like plot later on. Because I have not just transducer A, B, I have thousands of them, and when I select say ten transducers in the field I want to simply have carried forward measure for each and another one that sums them up together within each day instance. Hope my question makes sense.

Appreciate your help tremendously on this!

[skithian_]

This actually worked, thank you so much! What resources do you recommend to get better at PowerBI stuff ? I want to be as fluent as possible! This is so cool.