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!
- GeraldGEmerick
Memorable Member
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
- Shahid12523
Community Champion
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
