Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pedroccamaraDBI
Post Partisan
Post Partisan

current year, month and week

Hi guys
I'm trying to calculate the current year, current month and current week, ignoring the filter year and month on the report.
They work only if i don't choose any year and/or month in the report filter.

Current Year Sales =
CALCULATE( [Net Sales],
ALL('Tab Dates'),
'Tab Dates'[Year] = MAX('Tab Dates'[Year]))
 
Current Month Sales =
CALCULATE( [Net Sales],
ALL('Tab Dates'),
'Tab Dates'[Year] = MAX('Tab Dates'[Year]) &&
'Tab Dates'[MonthOfYear] = MAX( 'Tab Dates'[MonthOfYear] ))
Current Week Sales =
CALCULATE( [Net Sales],
ALL('Tab Dates'),
'Tab Dates'[Year] = MAX('Tab Dates'[Year]) &&
'Tab Dates'[ISO Weeknumber] = MAX( 'Tab Dates'[ISO Weeknumber] ))

Also, i've tried with a simple calculate and filter with my calendar table and it didn't work also.

Current Month Sales2 = CALCULATE( [Net Sales], 'Tab Dates'[MonthCompleted] = FALSE() )

Please, can anyone help me? If so, would you explain me the logic?
Thanks a lot!!
1 ACCEPTED SOLUTION

Hey @pedroccamaraDBI. Sorry, I think I missed the part about ignoring the month filter also. I attached a .pbix here with mock data, and added: ALL('Tab Dates' )  in each "VAR Result =", for example "VAR ResultCALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )". So the measures look as follows:

 

 

Current Year Sales = 
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
    CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )
RETURN
    Result

Current Month Sales = 
VAR MaxDateWithSales =
    CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
    CALCULATE(
        [Net Sales],
        ALL('Tab Dates'),
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[MonthOfYear] = MaxMonth
    )
RETURN
    Result

Current Week Sales = 
VAR MaxDateWithSales =
    CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxWeek =
    CALCULATE(
        VALUES('Tab Dates'[ISO Weeknumber] ),
        FILTER( ALL('Tab Dates'), 'Tab Dates'[Date] = MaxDateWithSales )
    )
VAR Result =
    CALCULATE(
        [Net Sales],
        ALL('Tab Dates' ),
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[ISO Weeknumber] = MaxWeek
    )
RETURN
    Result

 

 


And the results look like:

SteveHailey_2-1641218379641.png

 

Does that work?

 

View solution in original post

6 REPLIES 6
SteveHailey
Solution Specialist
Solution Specialist

Hello @pedroccamaraDBI

Looking at your first measure, Current Year Sales: The "MAX('Tab Dates'[Year])" is calculating the max year in the current filter context. So if you have a report filter it will only calculate the maxmium year that is visible in that filter.

I think you instead want something that looks like this:

 

VAR MaxYear = CALCULATE( MAX( 'Tab Dates'[Year] ), ALL('Tab Dates' ) )
VAR Result =
    CALCULATE( [Net Sales], 'Tab Dates'[Year] = MaxYear )
RETURN
    Result

 

This will calculate the max year in your date table, ignoring the current filter context. And your other monthly and weekly measures will use the same concept.

-Steve

Hey @SteveHailey 
Thank you so much for your answer. Let me ask you 2 things:
1. Because it would be a diferent measure how would you do it for current month, knowing that we have to filter besides the month, the year also.
2. How will it work, this current month measure, because the max month my not be the last with data, right? My calendar table has all the other months after that one, you know?
Thanks a lot and looking forward to see your answer
Best regards

Hey @pedroccamaraDBI. It sounds like you're defining the current date as the last date with sales data. In that case, I think something like the following should work:

 

Current Year Sales = 
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
    CALCULATE( [Net Sales], 'Tab Dates'[Year] = MaxYear )
RETURN
    Result


Current Month Sales = 
VAR MaxDateWithSales =
    CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
    CALCULATE(
        [Net Sales],
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[MonthOfYear] = MaxMonth
    )
RETURN
    Result


Current Week Sales =
VAR MaxDateWithSales =
    CALCULATE ( MAX ( 'Sales'[Order Date] ), ALL ( 'Sales' ) )
VAR MaxYear =
    YEAR ( MaxDateWithSales )
VAR MaxWeek =
    CALCULATE (
        VALUES ( 'Tab Dates'[ISO Weeknumber] ),
        FILTER ( 'Tab Dates', 'Tab Dates'[Date] = MaxDateWithSales )
    )
VAR Result =
    CALCULATE (
        [Net Sales],
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[ISO Weeknumber] = MaxWeek
    )
RETURN
    Result

 

Hello @SteveHailey 
I've just tested the first one, current year sales, and it didn't ignore my year and month filters as it should.
Just a reminder, i want to be able to see, everyday when i open this report, the total sales of this year, quarter, month and week.
Maybe it's missing somewhere, ALL ( Dates)...which i don't know where to put it in your measure.
Thanks a lot

Hey @pedroccamaraDBI. Sorry, I think I missed the part about ignoring the month filter also. I attached a .pbix here with mock data, and added: ALL('Tab Dates' )  in each "VAR Result =", for example "VAR ResultCALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )". So the measures look as follows:

 

 

Current Year Sales = 
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
    CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )
RETURN
    Result

Current Month Sales = 
VAR MaxDateWithSales =
    CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
    CALCULATE(
        [Net Sales],
        ALL('Tab Dates'),
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[MonthOfYear] = MaxMonth
    )
RETURN
    Result

Current Week Sales = 
VAR MaxDateWithSales =
    CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxWeek =
    CALCULATE(
        VALUES('Tab Dates'[ISO Weeknumber] ),
        FILTER( ALL('Tab Dates'), 'Tab Dates'[Date] = MaxDateWithSales )
    )
VAR Result =
    CALCULATE(
        [Net Sales],
        ALL('Tab Dates' ),
        'Tab Dates'[Year] = MaxYear,
        'Tab Dates'[ISO Weeknumber] = MaxWeek
    )
RETURN
    Result

 

 


And the results look like:

SteveHailey_2-1641218379641.png

 

Does that work?

 

This is awesome Steve.
Thank you so much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors