Input, output and stock in PowerBI using DAX

[CrgJnr]

I have a dataset in which each row correspond to a ticket.

I have 3 columns: ID of ticket, INPUT_VOLUMES, INPUT_DATE for the ticket arrival date, OUTPUT_VOLUMES, OUTPUT_DATE for the ticket closure date.

I need to calculate in PowerBI a trend of input volumes a trend of output volumes and a trend of stock volumes (difference between input and output volumes).

My difficulty is in wanting to use a general calendar date and not the input and output dates, since there are 2 I wouldn't know how to relate them.

Also, my data model contains about 30 millions of rows.

How can I proceed?

Thank u!

[Anonymous]

Hi @CrgJnr ,

As @lbendlin  said, USERELATIONSHIP can specify relationships to be used in a particular calculation, such as the relationship that exists between columnName1 and columnName2.

Since you have a large amount of data, it is important to think about the performance of power bi when using it for operations such as aggregating and summarizing data, utilizing parameters and filters, and using a star schema. More recommendations and practices for working with big data can be found in the documentation: Handling Big Data: Performance and Limits of Power BI (alphabold.com), Mastering Large Dataset Handling in Power BI📊 : Best Practices 💡 | by S Logeshkarthik | Medium.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The standard pattern is to link  the calendar table to the created data in an active relationship and to the closed date in an inactive relationship. Then use USERELATIONSHIP in your "closed this period"  measure and stock measure.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...