Forum Discussion
Visual with Slicer and Switch Help Request
Still very new to PBI / Dax and beating my head against a wall... I am creating a bar chart visual in PBI that will display the following on their own bar in the chart. I am looking to slice these bars (categories) based on contract year.
Bars / Categories:
- Invoices Submitted
- (Sum of Amount Where Submitted date is not blank)
- Invoices Accepted
- (Sum of Amount Where Submitted date is not blank AND Accepted Date is not blank)
- Invoices Deposited
- (Sum of Amount Where Deposited date, Submitted date, AND Accepted Date is not blank)
- Invoices Paid
- (Sum of Amount Where Paid date, Deposited date, Submitted date, AND Accepted Date is not blank)
This is a customers tracking spreadsheet that I can transform data upon import, but cannot reformat. As you can see, they are tracking invoice progression based on date. They need to know how much has been submitted, accepted, deposited, and paid. Additionally, they want to be able to slice these bars based on pre-defined contract years (originally they wanted standard YTD, MTD, etc.. and I had this working but cannot get it to work now with the contract years).
This is what I have thus far and I am open to any help anyone provides!
Period Table:
- Using this as my slicer and it allows for Select All or Contract Year Options with Multi-Select)
ContractYear_ID | ContractYear |
1 | Base |
2 | Year1 |
3 | Year2 |
4 | Year3 |
5 | Year4 |
Date:
Date =
CALENDAR(DATE(2023, 1, 1), DATE(2029, 6, 30))
Date | ContractYear_ID |
30-Jun-25 | 1 |
29-June25 | 1 |
etc.. | etc.. |
Measure : Invoices Submitted
- (other categories have the same measures referencing their correct date field. Ie: [Accepted Date], [Deposited Date] etc.)
Invoices Submitted = CALCULATE(
SUM(' Invoices'[Amount ($)]),
FILTER(
' Invoices',
NOT(ISBLANK('Invoices'[Submitted Date])
)
))
Previous Switch (obviously won’t work)
"Period Invoices Submitted =
SWITCH([Selected Period],
1, IF(ISBLANK(TOTALMTD([Invoices Submitted], DATESMTD(ALL('Invoices'[Submitted Date])))), 0, TOTALMTD([Invoices Submitted], DATESMTD(ALL('Invoices'[Submitted Date])))),
2, IF(ISBLANK(TOTALQTD([Invoices Submitted], DATESQTD(ALL('Invoices'[Submitted Date])))), 0, TOTALQTD([Invoices Submitted], DATESQTD(ALL('Invoices'[Submitted Date])))),
3, IF(ISBLANK(TOTALYTD([Invoices Submitted], DATESYTD(ALL('Invoices'[Submitted Date])))), 0, TOTALYTD([Invoices Submitted], DATESYTD(ALL('Invoices'[Submitted Date])))),
4, IF(ISBLANK(TOTALYTD([Invoices Submitted], PREVIOUSYEAR(DATESYTD(ALL('Invoices'[Submitted Date]))))), 0, TOTALQTD([Invoices Submitted], DATESQTD(ALL('Invoices'[Submitted Date])))),
5, [Invoices Submitted]
)"
Obviously the switch above will not work as it references pre-defined functions of TOTALYTD, DATESYTD, etc.
GOAL:
To create a dynamic bar chart that will represent the totals for each catergory based on the slicer selection.. with the ability to drill down and see the record data that the totals were derived from.
Mockup: (When Base Year is Selected)
When Year 1 is selected: (Year 1 is the current year)
Thank you in advance for any guidance provided!!