Sales Calculation Using Fiscal Dates

Sales CM = 

VAR Exact_Fiscal_Dates = CALCULATETABLE(
    SUMMARIZE(
        'Dates',
        'Dates'[TID_FY],
        'Dates'[TID_FQ],
        'Dates'[TID_FM],
        'Dates'[TID_FW],
        'Dates'[TID_CD],
        'Dates'[FiscalPeriod],
        'Dates'[Dates],
        "FY_Num", 'Dates'[TID_FY],
        "FQ_Num", VALUE(RIGHT('Dates'[TID_FQ], 1)),
        "FM_Num", VALUE(RIGHT('Dates'[TID_FM], 2)),
        "FW_Num", VALUE(RIGHT('Dates'[TID_FW], 2))
    )
)

VAR TxLastDate = LASTDATE(
    'Sales'[Date])

// Determine Facts about LastDate per Sales Transactions.
// Fiscal Encoding of Last Transaction Date

VAR Fiscal_encoding_Last_Tx = FILTER(
    Exact_Fiscal_Dates,
    Dates[Dates] = TxLastDate
)


VAR Year_LastDate = SUMMARIZE(
    Fiscal_encoding_Last_Tx,
    Dates[TID_FY]
)

VAR Quarter_LastDate = SUMMARIZE(
    Fiscal_encoding_Last_Tx,
    Dates[TID_FQ]
)

VAR Month_LastDate = SUMMARIZE(
    Fiscal_encoding_Last_Tx,
    [FM_Num]
)

VAR Week_LastDate = SUMMARIZE(
    Fiscal_encoding_Last_Tx,
    Dates[TID_FW]
)

VAR Day_LastDate = SUMMARIZE(
    Fiscal_encoding_Last_Tx,
 Dates[Dates]
)

// Setup the Current Fiscal Year using the Latest Fiscal Day.
VAR Exact_FY_Dates = FILTER(
    Exact_Fiscal_Dates,
    Dates[TID_FY] = Year_LastDate &&            // Captures the Fiscal Year Period based on the Fiscal Year of the Last Transaction.
    Dates[Dates] <= Day_LastDate                 // Bounded by the last day of the Transaction in the Current Fiscal Year.
)

// Capture the subset of the Fiscal Quarter where the last Fiscal Day is in.
VAR Exact_FQ_Dates = FILTER(
    Exact_FY_Dates,
    Dates[TID_FQ] = Quarter_LastDate            // Capture the Fiscal Quarter Period based on the Fiscal Quarter of the Last Transaction.
   
)

// Capture the subset of the fiscal Month from the Fiscal Quarter based from the last Fiscal Day is in.
VAR Exact_FM_Dates = FILTER(
    Exact_FQ_Dates,
    [FM_Num] = Month_LastDate               // Capture the Fiscal Month Period based on the Fiscal Month of the Last Transaction
)

// Capture the subset of the fiscal Week from the Fiscal Month based from the last Fiscal Day is in.
VAR Exact_FW_Dates = FILTER(
    Exact_FM_Dates,
    Dates[TID_FW] = Week_LastDate &&            // Capture the Fiscal Week Period based on the Fiscal Week of the Last Transaction.
    Dates[Dates] <= Day_LastDate                 // Ensures that the FW returns only the valid dates within the Fiscal Week of the Last Transaction to avoid null values for dates without tx.
)

// Longer Term Aggregation Time Boundaries Based from Fiscal Dates not bounded by Last 3 Months Period
VAR Exact_L3M_Dates = FILTER(
    Exact_Fiscal_Dates,
    [FM_Num] IN {Month_LastDate - 2, Month_LastDate - 1, Month_LastDate} &&
    Dates[Dates] <= Day_LastDate
)

// Return the period based on the Exact FY Dates.
VAR Date_Period_Exact_FY = SELECTCOLUMNS(
    Exact_FY_Dates,
    Dates[Dates]
)

// Returns the period bounded by the FQ where the Last Fiscal Day is in.
VAR Date_Period_Exact_FQ = SELECTCOLUMNS(
    Exact_FQ_Dates,
    Dates[Dates]
)

// Returns the period bounded by the FM where the Last Fiscal Day is in.
VAR Date_Period_Exact_FM = SELECTCOLUMNS(
    Exact_FM_Dates,
   Dates[Dates]
)

// Returns the period bounded by the FW where the Last Fiscal Day is in.
VAR Date_Period_Exact_FW = SELECTCOLUMNS(
    Exact_FW_Dates,
  Dates[Dates]
)

// Return the period based on the Exact L3M Dates.
VAR Date_Period_Exact_L3M = SELECTCOLUMNS(
    Exact_L3M_Dates,
    Dates[Dates]
)

// Returns the Calendar dates for active FY for only complete weeks within the FY.
VAR Date_Period_Complete_FW_FY_Period = SELECTCOLUMNS(
    FILTER(
        Exact_FY_Dates,
        'Dates'[TID_FW] IN
            SELECTCOLUMNS(
                CALCULATETABLE(
                    FILTER(
                        SUMMARIZE(
                            Exact_FY_Dates,
                            'Dates'[TID_FW],
                            "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                        ),
                        [FD_Count] = 7
                    )
                ),
                'Dates'[TID_FW]
            )
    ),
    'Dates'[Dates]
)

// Returns the Calendar dates for active Fiscal Quarter for only complete weeks within the FQ.
VAR Date_Period_Complete_FW_FQ_Period = SELECTCOLUMNS(
    FILTER(
        Exact_FQ_Dates,
        'Dates'[TID_FW] IN
            SELECTCOLUMNS(
                CALCULATETABLE(
                    FILTER(
                        SUMMARIZE(
                            Exact_FQ_Dates,
                            'Dates'[TID_FW],
                            "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                        ),
                        [FD_Count] = 7
                    )
                ),
                'Dates'[TID_FW]
            )
    ),
    'Dates'[Dates]
)

// Returns the Calendar dates for active Last 3 Fiscal Months for only complete weeks within the Last 3 Fiscal Months.
VAR Date_Period_Complete_FW_L3M_Period = SELECTCOLUMNS(
    FILTER(
        Exact_L3M_Dates,
        'Dates'[TID_FW] IN
            SELECTCOLUMNS(
                CALCULATETABLE(
                    FILTER(
                        SUMMARIZE(
                            Exact_L3M_Dates,
                            'Dates'[TID_FW],
                            "FD_Count", DISTINCTCOUNT( 'Dates'[Dates])
                        ),
                        [FD_Count] = 7
                    )
                ),
                'Dates'[TID_FW]
            )
    ),
    'Dates'[Dates]
)

// Returns the Calendar dates for active FM for only complete weeks within the FM.
VAR Date_Period_Complete_FW_FM_Period = SELECTCOLUMNS(
   
    FILTER(
        Exact_FM_Dates,
        'Dates'[TID_FW] IN
            SELECTCOLUMNS(
                CALCULATETABLE(
                    FILTER(
                        SUMMARIZE(
                            Exact_FM_Dates,
                            'Dates'[TID_FW],
                            "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                        ),
                        [FD_Count] = 7
                    )
                ),
                'Dates'[TID_FW]
            )
    ),
    'Dates'[Dates]
)

// Returns the Count of Complete FW in the active FY where the Last Tx is. Excludes weeks with incomplete # of days close to Last Tx Date.
VAR Count_of_Complete_FW_in_FY_Period =
COUNTROWS(
    // Returns a list of FW that has 7 days in it.  
    SELECTCOLUMNS(
        CALCULATETABLE(
            FILTER(
                SUMMARIZE(
                    Exact_FY_Dates,
                    'Dates'[TID_FW],
                    "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                ),
                [FD_Count] = 7
            )
        ),
        'Dates'[TID_FW]
    )
)

// Returns the Count of Complete FW in the active L3M where the Last Tx is. Excludes weeks with incomplete # of days close to Last Tx Date.
VAR Count_of_Complete_FW_in_L3M_Period =
COUNTROWS(
    // Returns a list of FW that has 7 days in it.  
    SELECTCOLUMNS(
        CALCULATETABLE(
            FILTER(
                SUMMARIZE(
                    Exact_L3M_Dates,
                    'Dates'[TID_FW],
                    "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                ),
                [FD_Count] = 7
            )
        ),
        'Dates'[TID_FW]
    )
)

// Returns the Count of Complete FW in the active L3M where the Last Tx is. Excludes weeks with incomplete # of days close to Last Tx Date.
VAR Count_of_Complete_FW_in_FM_Period =
COUNTROWS(
    // Returns a list of FW that has 7 days in it.  
    SELECTCOLUMNS(
        CALCULATETABLE(
            FILTER(
                SUMMARIZE(
                    Exact_FM_Dates,
                    'Dates'[TID_FW],
                    "FD_Count", DISTINCTCOUNT( 'Dates'[Dates] )
                ),
                [FD_Count] = 7
            )
        ),
        'Dates'[TID_FW]
    )
)

RETURN 
CALCULATE([Total Sales],
Sales[Date] in (Date_Period_Exact_FM)

)

Code Analysis

The provided DAX (Data Analysis Expressions) code is designed for Power BI and is intended to calculate the "Sales CM" (Current Month Sales). It involves complex date filtering and fiscal period calculations.

Key Variables

• Exact_Fiscal_Dates: This variable summarizes the 'Dates' table by fiscal periods (Year, Quarter, Month, Week), including some calculated fields (FY_Num, FQ_Num, FM_Num, FW_Num).

• TxLastDate: This variable holds the last date of the sales transaction using the LASTDATE function on 'Sales'[Date].

• Fiscal_encoding_Last_Tx: Filters Exact_Fiscal_Dates to get details for the last transaction date.

Summarizing Fiscal Periods

• Year_LastDate, Quarter_LastDate, Month_LastDate, Week_LastDate, Day_LastDate: These variables summarize the fiscal period details for the last transaction date.

Filtering Periods

• Exact_FY_Dates: Filters Exact_Fiscal_Dates for the current Fiscal Year up to the last transaction date.
• Exact_FQ_Dates: Filters Exact_FY_Dates for the current Fiscal Quarter.
• Exact_FM_Dates: Filters Exact_FQ_Dates for the current Fiscal Month.
• Exact_FW_Dates: Filters Exact_FM_Dates for the current Fiscal Week, ensuring only valid dates are included.
• Exact_L3M_Dates: Filters Exact_Fiscal_Dates for the last 3 months up to the current Fiscal Month.

Selecting Columns

• Date_Period_Exact_FY, Date_Period_Exact_FQ, Date_Period_Exact_FM, Date_Period_Exact_FW, Date_Period_Exact_L3M: These variables use the SELECTCOLUMNS function to extract the dates for various fiscal periods.

Complete Week Periods

• Date_Period_Complete_FW_FY_Period, Date_Period_Complete_FW_FQ_Period, Date_Period_Complete_FW_L3M_Period, Date_Period_Complete_FW_FM_Period: These variables filter for complete weeks (7 days) within the respective periods.

Counting Complete Weeks

• Count_of_Complete_FW_in_FY_Period, Count_of_Complete_FW_in_L3M_Period, Count_of_Complete_FW_in_FM_Period: These variables count the number of complete weeks within the respective fiscal periods.

Return Statement

The final RETURN statement calculates the total sales for dates within the current fiscal month (Date_Period_Exact_FM).

Simplified Explanation

1. Initialization: The code starts by summarizing the 'Dates' table to capture fiscal period details.
2. Last Transaction Date: It finds the last sales transaction date.
3. Fiscal Encoding: Filters to determine fiscal details of the last transaction.
4. Creating Date Ranges: Filters to create fiscal periods ranging from the year, quarter, month, and week.
5. Complete Weeks: Further filters to include only complete weeks in fiscal periods.
6. Sales Calculation: Finally, the code calculates the sales for dates within the current fiscal month.