Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have the following situation:
1 table named Forecast that has multiple date columns, simplified for this example
1. Start Date
2. Close date
In addition, I created a Dates table with 'Date = CALENDAR(MIN(Forecast[Start Date];MAX(Forecast[Start Date])
Then created one-to-many relationship from Date to Start Date (active), Date to Expected Order date (inactive), Close date (inactive).
What I want to show in 1 graph;
A) The sum of Sales based on Close date
B) The sum of Sales based on Start date
Meaning that if we have:
Start Date | End Date | Sales
01-02-2019 | 05-04:2019 | 500,00
I want to draw a graph with Date on the axis, that shows A in April and B in February.
However I'm failing to get there.
What I tried was the following formula for A:
Thinking that if MNDate and MXDate are determined by the Date axis (if showing by month, april 2019 would have a MIN of april 1st and Max of April 30th. And then sum when the Close Dates fall in that range. But that doesn't help displaying it in April, rather than the corresponding month of the Start date.
If I switch the active relationship to Close Date, then it is shown in the right month for A, but not for B.
Is this at all possible? I thought I'd seen a report that works like this.
I think you should able to do with an independent date slicer.
You can get a reference from the article. You might be able to do with only one slicer.
https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613
Hmm not succeeding yet unfortunately. The article uses one single date column, and it is useful for comparing the same measure from different periods. But I have one table with 2 different date columns and want to plot them simultaneously.
So let's say we have 2 orderlines from an orders table. Including Intake Date and value and Shipment date and value (which can be lower on incomplete delivery) of the order:
Order No | Intake Date | Intake Value | Shipment Date | Shipment Value
1 | March 3 2019 | 5.000 | April 12 2019 | 4.000
2 | April 20 2019 | 10.000 | May 2 2019 | 10.000
I want to plot both intake and shipment on a single date axis in a single graph.
So in a graph showing March, April, May 2019;
March needs to show an intake of 5.000 and a shipment of 0
April needs to show an intake of 10.000 and a shipment of 4.000
May needs to show an intake of 0 and a shipment of 10.000
Try this, you can create more than on relation in power BI. Few of them can be inactive. Means, join both dates with the same time dim and keep one active.
This sample formula to use one relation at a time
Sales 7 = VAR Column1= SELECTEDVALUE ( dim[DimColumn1]) VAR Column2 = SELECTEDVALUE ( Sales[Second]) var Column3 =SUMMARIZE(Sales,Sales[Column1]) var Column4 =SUMMARIZE(Sales,Sales[Column2]) RETURN CALCULATE( SUM (Sales[Sales]), dim[DimColumn1] in Column3 ,USERELATIONSHIP(dim[DimColumn1],Sales[First]) )+CALCULATE( SUM (Sales[Sales]), dim[DimColumn1] in Column4 ,USERELATIONSHIP(dim[DimColumn1],Sales[Second]) )
Sorry, but could you show example data with it? I'm a bit confused what column1, column2 etc are meant to be.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |