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.
I'm just a new guys on DAX. I have a function of sales: P= ((S0+S3)/2+S1+S2)/3
S0: Last month of previous quarter.
S1: First month of this quarter.
S2: second month of this quarter.
S3: last month of this quarter.
DATA run over years to years. Can anyone can help me how to calculate that, pls @@
Solved! Go to Solution.
Hi @philongxpct,
First, you should create calculated columns month, quarter, the first month of this quarter, the second month of this quarter, the end month of this quarter.
MonthOfYear = MONTH(Table1[Date]) Quarter = ROUNDUP(MONTH([Date])/3, 0) Firstt = MONTH(STARTOFQUARTER(Table1[Date])) End = MONTH(ENDOFQUARTER(Table1[Date])) Second month = (CALCULATE(MAX(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])) +CALCULATE(MIN(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])))/2
Then create measures using the following formulas.
S1 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Firstt])) S2 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Second month])) S3 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[End])) S0 = CALCULATE(Table1[Last],PARALLELPERIOD(Table1[Date],-1,QUARTER))
If you have other issues, don't hesitate to let me know.
Best Regards,
Angelia
Hi @philongxpct,
First, you should create calculated columns month, quarter, the first month of this quarter, the second month of this quarter, the end month of this quarter.
MonthOfYear = MONTH(Table1[Date]) Quarter = ROUNDUP(MONTH([Date])/3, 0) Firstt = MONTH(STARTOFQUARTER(Table1[Date])) End = MONTH(ENDOFQUARTER(Table1[Date])) Second month = (CALCULATE(MAX(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])) +CALCULATE(MIN(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])))/2
Then create measures using the following formulas.
S1 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Firstt])) S2 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Second month])) S3 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[End])) S0 = CALCULATE(Table1[Last],PARALLELPERIOD(Table1[Date],-1,QUARTER))
If you have other issues, don't hesitate to let me know.
Best Regards,
Angelia
Hi @philongxpct,
If you have resolved your issue, please mark the corresponding reply as answer, which will help more people. If you still have issue, please feel free to ask.
Best Regards,
Angelia
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |