Forum Discussion
Period DAX Measure
Hi Experts
I have created a discount table in order to work out the following measure....in order to work out sales....for the following periods
Daily, Week, Monthly and Yearly and all Date
not sure how to write the measure - help please...
Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.Hi, Anonymous ;
1. Enter data table.
2.create a measure.
Every-sales = var _year=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))) var _month=CALCULATE(SUM([sales]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))) var _week=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date]))&&WEEKNUM([Date],2)=WEEKNUM(MAX([Date]),2))) return SWITCH(MAX('slicer'[slicer]), "daily",SUM('Table'[sales]), "Weekly",IF(WEEKDAY(MAX([Date]),2)=1,_week), "month",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))),_month), "year",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))),_year), "total",SUMX(ALL('Table'),[sales]))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi, Anonymous ;
You could create a slicer table ,then create a measure as follows:
1. enter a slicer table.
2.create a measure.
Every-sales = var _year=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))) var _month=CALCULATE(SUM([sales]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))) var _week=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date]))&&WEEKNUM([Date],2)=WEEKNUM(MAX([Date]),2))) return SWITCH(MAX('slicer'[slicer]), "daily",SUM('Table'[sales]), "Weekly",IF(WEEKDAY(MAX([Date]),2)=1,_week), "month",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))),_month), "year",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))),_year), "total",SUMX(ALL('Table'),[sales]))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Support can the following be done??
https://community.powerbi.com/t5/Desktop/data-Slicer-not-to-impact-my-Period-Slicer-for-WEEK-MTD-YTD-only/m-p/2295394#M831481
Hi, Anonymous ;
1. Enter data table.
2.create a measure.
Every-sales = var _year=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))) var _month=CALCULATE(SUM([sales]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))) var _week=CALCULATE(SUM([sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date]))&&WEEKNUM([Date],2)=WEEKNUM(MAX([Date]),2))) return SWITCH(MAX('slicer'[slicer]), "daily",SUM('Table'[sales]), "Weekly",IF(WEEKDAY(MAX([Date]),2)=1,_week), "month",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0))),_month), "year",IF(MAX([Date])=CALCULATE(MIN([Date]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])))),_year), "total",SUMX(ALL('Table'),[sales]))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.Anonymous , if you want to change Axis, then you need bookmarks
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://www.youtube.com/watch?v=6jeSIRpjv0Mmeasure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern