Calculate Sum of a period every year

[Anonymous]

Hello,

I have an issue in calculating sum of quantity same period for all years. I have currently 3 years data in my table where 2019 data has only months from Jan to July. So all the years should show the data only for months Jan to July. But in the calculation its taking from Jan to dec for all years. 
I have list of measures that i have written. Please look into it and let me know what I am missing.

minMonth = CALCULATE(MIN(Table1[Date]), FILTER(Table1, Table1[Date].[Year]=MAX(Table1[Date].[Year])))

maxMonth = CALCULATE(MAX(Table1[Date]))

quantity = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1, Table1[Date] >= Table1[minMonth]), FILTER(Table1, Table1[Date]<= Table1[maxMonth]))
Thanks in Advance. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As tested, i create measures below

my data table

measure [Current year] is the last year which you want to show on the visual, you could change it dynamically,

current year = 2019

avalue all years = SUM('Table'[value])

min month =
CALCULATE (
    MIN ( 'Table'[month] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[value] <> BLANK ()
            && [year] = [current year]
    )
)


max month =
CALCULATE (
    MAX ( 'Table'[month] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[value] <> BLANK ()
            && 'Table'[year] = [current year]
    )
)

value per year =
CALCULATE (
    [avalue all years],
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[year] = MAX ( 'Table'[year] )
            && 'Table'[date]
                < DATE ( 'Table'[year], [max month] + 1, 1 )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Could you accept to create three measures for three years(2017, 2018, 2019)?

Best Regards
Maggie

[Anonymous]

Hi @v-juanli-msft ,

I can Accept but my Years in future may vary.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As tested, i create measures below

my data table

measure [Current year] is the last year which you want to show on the visual, you could change it dynamically,

current year = 2019

avalue all years = SUM('Table'[value])

min month =
CALCULATE (
    MIN ( 'Table'[month] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[value] <> BLANK ()
            && [year] = [current year]
    )
)


max month =
CALCULATE (
    MAX ( 'Table'[month] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[value] <> BLANK ()
            && 'Table'[year] = [current year]
    )
)

value per year =
CALCULATE (
    [avalue all years],
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[year] = MAX ( 'Table'[year] )
            && 'Table'[date]
                < DATE ( 'Table'[year], [max month] + 1, 1 )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the reply. I have tried the same as said above.

If I hardcode [current year] = 2019 then I get the expected output.
If I dynamically write a measure  [current year] = YEAR(CALCULATE(MAX(EtrxTrxDetails[EtrxMonth])))  
then the output is not as expected.

Why is the dynamic value for the current year is not taken as 2019 ?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure

Measure = CALCULATE(MAX('Table'[year]),ALLSELECTED('Table'))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the solution @v-juanli-msft 

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft and @amitchandak ,

Sorry for late reply. I tried with Date table but I think i dont need Date table because in my table  'Table1' Date column the date is in the format of mmm-yy. I dont have day in my date column Table1. 

So actually what i have done is, I created a new column with monthNumber. 

monthNumber = MONTH(Table1[Date])

and I have rewritten the above mentioned measures like this.
minMonth = CALCULATE(MIN(Table1[Date]), FILTER(Table1, Table1[Date].[Year]=MAX(Table1[Date].[Year])))
maxMonth = CALCULATE(MAX(Table1[Date]))

quantity = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1, Table1[monthNumber] >= Table1[minMonth]), FILTER(Table1, Table1[monthNumber]<= Table1[maxMonth]))

I can see the output of minMonth and maxMonth as 1 and 7 respectively when I have used this in multi-row card.

Still the quantity is not calculated by the minMonth and maxMonth. But when I hardcode the values in the measure like below. Then I get the expected result.
quantity = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1, Table1[monthNumber] >= 1), FILTER(Table1, Table1[monthNumber]<= 7))

Can you please help me know Why the value is not taken by using measure minMonth and maxMonth ?
Thanks in Advance. 

[amitchandak]

Better YTD measures. datesYTD and Total YTD. If you select july in selection it will control. else put additional filter

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Or
YTD Sales = 
var _max = max(date[month_no])
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"), dates[monthno]<=_max )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/