Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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  ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.


  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

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.