Hi there
I`m working in a new report that I need to show the users when a non-compliance happens in a particular transaction .
To do this I have a transaction table that has 2 dates (Start transaction date and record end date) and a second separated table that show when the non-compliance happened.
On the previous system that were created using R languange it was possible to create a line showing the start and end date of the transaction and the red dots were when the non-compliance happened.
I would like to create view, with a bar chart showing the range date ( Transaction table - start and end date) and a line chart showing when the non-compliance happens .
Unfortunately, this is a confidential data, and I cannot share the pbix but please following what I tried to achieve , the tables example and the steps that I already did:
Transaction Table
transaction | Start transaction date | End transaction date |
T1 | 18/07/2021 | 17/12/2021 |
T2 | 18/07/2021 | 17/12/2021 |
Non-compliance Table
transaction | Non -compliance date |
T1 | 14/07/2021 |
T2 | 15/07/2021 |
On the above case I expect a graphic with a line starting on 18/07/2021 finishing on 17/12/2021 and a line mentioned that the transaction T1 had a non-compliance on 14/07/2021 and the T2 had a non-compliance on 15/07/2021
I did the following steps and I still facing an issue to find a correct dax calculation :
1 – Created a calendar table where the min date is (Start transaction date – from the Transaction table) and the MAX date is (End transaction date – from the Transaction table)
2 – Create a relationship between the Calendar date with the Transaction table using the start date as INACTIVE relationship
3 – Create a relationship between the Calendar date with the Transaction table using the ends date as ACTIVE relationship
4 – Created the following dax code :The issue with this code is that I could not find a way to show the range date (start and end) because the relationship between the transaction and date tables are active just
Calculation
VAR LastVisibleDate = MAX (' Transaction Table [wp_sys_modified_on])
VAR FirstVisibleDate = MIN ( Transaction Table [transaction date] )
VAR LastDateWithTotal =
CALCULATE (
MAX ( 'Calendar'[Date] ),
KEEPFILTERS(ALLSELECTED('Calendar'[Date] )
))
VAR Result =
IF (
FirstVisibleDate <= LastDateWithTotal,
CALCULATE (
DISTINCTCOUNT(Transaction Table [transaction]) ,
'Calendar'[Date] <= LastVisibleDate
)
)
RETURN
Result
Can you please help me to create view, with a bar chart showing the range date ( Transaction table - start and end date) and a line chart showing when the issue happens .
Thank in advance
