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
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
        ),