Fiscal Calendar (4-4-5) Requirements DAX

[tkramer]

Hey everyone,

I have been struggling for a few weeks trying to move my current reports from the Standard Calendar to our company's 4-4-5 Fiscal Calendar. 

In the attached PBIX file, I have 2023 and 2024 Fiscal Calendars loaded, I have shown what the problem is, and what the expected results should be.

These reports work fantastic on the Standard Calendar, but I can not get the correct measure for my 4-4-5!  Help would be extremely appreciated!  If you could try your measures in the PBIX file and then send back, that would be awesome.  

Here is the link to download from Google Drive:
https://drive.google.com/file/d/1rlCmeluJKptGdRL4FM6A49cocbJjSET3/view?usp=sharing

Order Value PY = 
    VAR _CY = SELECTEDVALUE( 'DIM-Date'[F Year] )
    VAR _PY = _CY - 1
    VAR _Result = 
        CALCULATE(
            [Order Value],
            'DIM-Date'[F Year] = _PY
        )

    RETURN
        _Result

[v-tangjie-msft]

Hi @tkramer ,

You can create a measure.

Measure 3 = 

     VAR _CY = MAX( 'DIM-Date'[F Year] )
     VAR _PY = _CY - 1
     VAR _Result = 
         CALCULATE(
             [Order Value],
             FILTER(ALL('DIM-Date'),'DIM-Date'[F Year] = _PY && 'DIM-Date'[F Day of Year] =MAX('DIM-Date'[F Day of Year]))
         )

     RETURN
         _Result

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[tkramer]

This Total on the bottom right does not sum correctly. It is only populating 1.2M.

[tkramer · Accepted solution]

Chat GPT and I figured it out 👏 I appreciate your help

Total Sales PY = 
VAR _PreviousYear = MAX('DIM-Date'[F Year]) - 1
VAR _CurrentDayOfYear = VALUES('DIM-Date'[F Day of Year])

RETURN
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('DIM-Date'),
            'DIM-Date'[F Year] = _PreviousYear &&
            'DIM-Date'[F Day of Year] IN _CurrentDayOfYear
        )
    )

.  It was the use of the VALUES function to create a virtual table and the IN function.