Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
1 year ago

Measure to count a value from different tables with differing date relationships (pls read)

Hi, I have below DAX that creates a table. The Financial Year variables are calculated in Power Query. For example, the __FYS will be fixed at 01/08/2023, __FYE will be 31/07/2024. The __PFYS and __...
Need Help
techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi D_PBI 

as you already have a disconnected table for the measure selector, a dynamic measure can be used to switch between the various calculations based on the user's selection.

 

Here's an example

 

Dynamic Metric =
VAR SelectedMeasure = SELECTEDVALUE('Measure Selector'[Measure Name])
 
VAR CommonFilter =
    FILTER(
        ALL('agreement'),
        agreement[Agreement Type] = 100000024 &&
        (
            CONTAINSSTRING(agreement[Agreement Status], "Preliminary") ||
            CONTAINSSTRING(agreement[Agreement Status], "Full Application") ||
            CONTAINSSTRING(agreement[Agreement Status], "Submitted")
        )
    )

RETURN
SWITCH(
    TRUE(),
   
    SelectedMeasure = "Funding Applications",
        CALCULATE(
            DISTINCTCOUNT(agreement[agreementid]),
            USERELATIONSHIP('date'[Date], agreement[Execution Date]),
            CommonFilter
        ),

    SelectedMeasure = "Potential Value",
        CALCULATE(
            SUM(agreement[Value]),
            USERELATIONSHIP('date'[Date], agreement[Execution Date]),
            CommonFilter
        ),

    BLANK()
)
  • D_PBI's avatar
    D_PBI
    Icon for Post Partisan rankPost Partisan
    1 year ago

    techies thank you for your help. I shall try this approach but I think I also need help on the date filtering logic. The DATESBETWEEN and SAMEPERIODLASTYEAR functions don't seem to be doing as I need. Do I have the use of these functions wrong with their intended use against various nodes of the dimDate table in the Matrix (i.e. Year, Quarter, Month)?

    • techies's avatar
      techies
      Icon for Super User rankSuper User
      1 year ago

      Hi D_PBI i guess you can modify your dynamic metric to include previous year comparisons using SAMEPERIODLASTYEAR like this ---- 

      VAR PY_Dates = SAMEPERIODLASTYEAR(VALUES('date'[Date]))
       
      and then using it inside TREATAS() to filter the fact table (agreement[Execution Date]) based on the previous year's date
       
       SelectedMeasure = "Potential Value (Previous Year)",
              CALCULATE(
                  SUM(agreement[Value]),
          TREATAS(PY_Dates, agreement[Execution Date]),
                  CommonFilter
              ),