Forum Discussion
Visual with Slicer and Switch Help Request
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]
)
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 | 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!
Memorable MemberAnonymous 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