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!
Community ChampionUse 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