Iterating Daily Sales

[ronaldbalza2023]

Hi everyone, I am just wondering why my sales has been aggregated and it not showing sum of daily sales using the code below? Basically, I wanted to show the daily sales of a particular financial year regardless of what the filter is applied (that is why I used ALL function). Thanks in advance 

Turnover FY20 = 
CALCULATE (
    [Turnover Amount],
    ALL ( 'Custom Dates Calendar'[Date] ),
    'Custom Dates Calendar'[FW Year] = "FY 2020"
)

Turnover Amount = SUM(Sales[Turnover])

Sample PBIX.pbix 

[ronaldbalza2023 · Accepted solution]

This code solved my scenario. 😉

Turnover FY20 = 
VAR FYnum = 2020
VAR DayInFYnum = VALUES( 'Custom Dates Calendar'[FW DayOfYearNumber] )
RETURN

CALCULATE( [Turnover Amount],
    REMOVEFILTERS('Custom Dates Calendar'),
    'Custom Dates Calendar'[FW DayOfYearNumber] in DayInFYnum,
    'Custom Dates Calendar'[FW YearNumber] = FYnum
)

[tamerj1]

Hi @ronaldbalza2023 
Yes because you are removing the filter only from the Date column. But there are other filters coming from other columns (the fiscal week and the fiscal month). In this case better to remove the filter completely fromthe date table

Turnover FY20 = 
CALCULATE (
    [Turnover Amount],
    REMOVEFILTERS( 'Custom Dates Calendar' ),
    'Custom Dates Calendar'[FW Year] = "FY 2020"
)

[ronaldbalza2023]

Hi @tamerj1 , doesn't seem to work. Attached is the link from a sample pbix. Thanks for taking the time on this 😊

[tamerj1]

Nothing is attached! Please share a download link

[ronaldbalza2023]

https://www.dropbox.com/s/rfprvrd9ebrz1we/Sample%20PBIX.pbix?dl=0
thanks in advance 😊

[tamerj1]

@ronaldbalza2023 
Is this the result that you are looking for? You can use either ALL or REMOVEFILTERS to remove all the filters from the date table

[ronaldbalza2023]

Hi @tamerj1 , unfortunately not but appreciate your effort.  Do you see the Turnover FY20 has a value of 119,246,151.59? That is the sum total for the whole financial year of 2020. I wanted to get the daily sales out of it similar to the column Turnover Amount (Current Filter) if that makes sense. Thanks 😊

[tamerj1]

@ronaldbalza2023 
I think I totally misunderstood your requirement. I hope this is what you are expecting to see https://we.tl/t-hrc4nl4a6C

Turnover 1 Year Ago = 
CALCULATE (
    [Turnover Amount],
    SAMEPERIODLASTYEAR ( 'Custom Dates Calendar'[Date] )
)

Turnover 2 Years Ago = 
CALCULATE (
    [Turnover Amount],
    SAMEPERIODLASTYEAR ( DATEADD ( 'Custom Dates Calendar'[Date], - 1, YEAR ) )
)