Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks in Advance.
Solved! Go to 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 )
)
)
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.
Hi @Anonymous
Could you accept to create three measures for three years(2017, 2018, 2019)?
Best Regards
Maggie
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 )
)
)
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 ?
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.
Hi @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.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |