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!!
Hi Anonymous as i understand, please try this,
after defining your measures for example, like this
Invoices Accepted =
CALCULATE (
SUM ( Invoices[Amount ($)] ),
NOT ISBLANK ( Invoices[Submitted Date] ),
NOT ISBLANK ( Invoices[Accepted Date] ),
TREATAS ( VALUES ( Period[ContractYear_ID] ), 'Date'[ContractYear_ID] ),
USERELATIONSHIP ( Invoices[Accepted Date], 'Date'[Date] )
)repeat for submitted, deposited and so on
then create a disconnected table
Category = DATATABLE (
"Category", STRING,
{
{ "Submitted" },
{ "Accepted" },
{ "Deposited" },
{ "Paid" }
}
)and the measure as this Invoice Amount by Category =
SWITCH (
SELECTEDVALUE ( Category[Category] ),
"Submitted", [Invoices Submitted],
"Accepted", [Invoices Accepted],
"Deposited", [Invoices Deposited],
"Paid", [Invoices Paid]
)
7 Replies
Hello everyone! I am new to DAX and I need to update a switch. My original switch was based on standard date periods (ie: MTD, YTD, etc). However, the requirement has changed and I need to switch based on contract years that are not calendar year based. I have a period Table that has the Periods Identified, and I have a date Table that includes a calculated column for the Period_ID. How do I then update my switch to capture the selected period and adjust the data based on the period selected?
Period Table:
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
Invoices Submitted = CALCULATE(
SUM(' Invoices'[Amount ($)]),
FILTER(
' Invoices',
NOT(ISBLANK('Invoices'[Submitted Date])
)
))
Previous Switch (need to update)
"Period Submitted =
SWITCH([Selected Period],
1, IF(ISBLANK(TOTALMTD([Measure Submitted], DATESMTD(ALL('Invoices'[Submitted to PAYOR])))), 0, TOTALMTD([Measure Submitted], DATESMTD(ALL('Invoices'[Submitted to PAYOR])))),
2, IF(ISBLANK(TOTALQTD([Measure Submitted], DATESQTD(ALL('Invoices'[Submitted to PAYOR])))), 0, TOTALQTD([Measure Submitted], DATESQTD(ALL('Invoices'[Submitted to PAYOR])))),
3, IF(ISBLANK(TOTALYTD([Measure Submitted], DATESYTD(ALL('Invoices'[Submitted to PAYOR])))), 0, TOTALYTD([Measure Submitted], DATESYTD(ALL('Invoices'[Submitted to PAYOR])))),
4, IF(ISBLANK(TOTALYTD([Measure Submitted], PREVIOUSYEAR(DATESYTD(ALL('Invoices'[Submitted to PAYOR]))))), 0, TOTALQTD([Measure Submitted], DATESQTD(ALL('Invoices'[Submitted to PAYOR])))),
5, [Measure Submitted]
)"
Obviously the switch above will not work as it references pre-defined functions of TOTALYTD, DATESYTD, etc. How do I adjust the switch to reference my defined date periods?
Thank you for looking and helping!
Anonymous I would think that you could do something like the following:
SWITCH( [Selected Period ], 1, CALCULATE( [Measure Submitted], 'Invoices'[Period ID] = "Year 1" ),Something along those lines.
Thank you for taking the time Gerald. I updated my Switch as you suggested but feel that I may not have provided enough information. Year 1 is defined in a separate table, therefore the date in the invoices table need to reference back to the date table, grab the ContractYear_ID that way it can be correlated back to the Contract Year, or Period. The invoice table itslef does not identify an invoice as Year 1 etc.
It is very possible that I have completely misunderstood your solution. I did attempt to make it work but was unable to do so.
I have further explained what I am working with thus far and what my end goal is in another post if you are interested in reviewing it and making more suggestions..
Visual with Slicer and Switch Help Request - Microsoft Fabric Community
Use your custom ContractYear_ID instead of TOTALYTD/MTD.
Example:
SelectedContractYearID =
SELECTEDVALUE('Period'[ContractYear_ID])Period Submitted =
VAR SelPeriod = [SelectedContractYearID]
RETURN
CALCULATE(
[Measure Submitted],
KEEPFILTERS(
FILTER('Date', 'Date'[ContractYear_ID] = SelPeriod)
)
)
👉 This way the slicer drives which contract year is applied, instead of fixed calendar functions.Forgive me for being so inexperienced. I tried your solultion by creating a measure against my period table for "SelectedContractYearID" as you indicated above. I then created a measure for Period Submitted as you indicated in your comment. Unfortunately this does not work in my scenario. I have updated my post with the "Submitted Measure" in hopes that you (or someone) can help. I am afraid after looking at the responses that the fault is mine for not including enough information. I have also created a bigger post to elaborating more on what my end goal is if you are interested.
Visual with Slicer and Switch Help Request - Microsoft Fabric Community
Hi Anonymous as i understand, please try this,
after defining your measures for example, like this
Invoices Accepted =
CALCULATE (
SUM ( Invoices[Amount ($)] ),
NOT ISBLANK ( Invoices[Submitted Date] ),
NOT ISBLANK ( Invoices[Accepted Date] ),
TREATAS ( VALUES ( Period[ContractYear_ID] ), 'Date'[ContractYear_ID] ),
USERELATIONSHIP ( Invoices[Accepted Date], 'Date'[Date] )
)repeat for submitted, deposited and so on
then create a disconnected table
Category = DATATABLE (
"Category", STRING,
{
{ "Submitted" },
{ "Accepted" },
{ "Deposited" },
{ "Paid" }
}
)and the measure as this Invoice Amount by Category =
SWITCH (
SELECTEDVALUE ( Category[Category] ),
"Submitted", [Invoices Submitted],
"Accepted", [Invoices Accepted],
"Deposited", [Invoices Deposited],
"Paid", [Invoices Paid]
)
