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.
Hi,
would like to establish the following view. Am not sure how I could make it more dynamic, being updated each month, without taking any information manually.
Could you please support on the DAX query formula so I could prepare a similar view?
Thank you. Atanas
Hi,
I assume your table look like below.
If you want to calculate the previous month and previous quarter’s sum cost,
You can add a month and quarter column from “fiscal month” column in query editor.
Now you can create 2 measures:
pervious month sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[month] = EARLIER(fiscalInfo[month]) + 1), fiscalInfo[cost in usd])))
pervious quarter sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[quarter] = EARLIER(fiscalInfo[quarter]) + 1), fiscalInfo[cost in usd])))
This is the result:
Best Regards
Alex
Good Work Alex.
But I would suggest to use Time Intelligence in such scenarios as your formulas are quite memory expensive and would not particularly suitable for large data sets. It is nesting the two iterator functions FILTER and SUMX.
Thanks & Regards,
Bhavesh
Hi @AlexChen
thank you for responding, immense help and works fine, so far. Three short questions:
1. How do you create the column Quarter?
2. If I would like to see information from previous years, for example August 2015, this would overstep with August 2016 in the column Month, and therefore, in the final numbers. How do I avoid it?
3. I have Report Level Filters, which seem to be not captured in the measures mentioned. Is there a way to do so?
Thanks, Atanas
Hi t-atgeor,
First question,
You can go to query editor -> add column to create year/month/quarter/week by your "fiscal month" if it's a date type.
Second question,
I realized the issue. I updated my dateset by adding data of last year.
Now add new columns Year/Month/Quarter with the steps above.
add another 4 calculated columns using code below.
currentMonth = [Year] & [Month]
currentQuarter = [Year] & [Quarter]
lastMonth = if(fiscalInfo[Month] = 1, (fiscalInfo[Year] - 1) & 12, fiscalInfo[Year] & (fiscalInfo[Month] - 1))
lastQuarter = if(fiscalInfo[Quarter] = 1, (fiscalInfo[Year] - 1) & 4, fiscalInfo[Year] & (fiscalInfo[Quarter] - 1))
add 2 measure to calculate previous month and previous quarter's sum amount.
pervious month sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[lastMonth] = EARLIER(fiscalInfo[currentMonth])), fiscalInfo[cost in usd])))
pervious quarter sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[lastQuarter] = EARLIER(fiscalInfo[currentQuarter])), fiscalInfo[cost in usd])))
This is the result.
Third question,
What filter have you put in Report level filter? I put "fiscal month" into it and found it captured.
Can you share a screenshot about it?
Best Regards
Alex
thanks a lot, helped me big time. Am only wondering whether I could amend the quarter columns using a business quarter. Sometimes, the company's fiscal year does not comprehend with the calendar year, the fiscal year in my case starts in October.
E.g Quarter 4 of calendar year 2016 = Quarter 1 of fiscal year 2017
How do I show the correct quarter in this case?
Secondly, lets assume I have a slicer for the month and pick up February, my column Previous month shows me an error and not the cost of January. How do I amend the formula to have it flexible, based on the slicer Fiscal Month?
Thanks and regards,
Atanas
Hi Atanas,
This video tutorial by Alberto Ferrari will guide you create the calculations you requested. Yes, this calculations are possible in PBI using DAX and they are called time intelligence functions.
Please watch to get indepth knowllege of Time Intelligence.
Thanks & Regards,
Bhavesh
I haven't found video in the enclosed link
Hi Bhavesh,
I am very new to DAX and PowerBI and need help to get values on card upto current quarter
I have a table with Column DPQuarter which have Q1, Q2, Q3 and Q4 respectively and my Q1 starts from April month. I have to show balances in each Quarter until this Quarter as follows
Q1(Apr, may, jun)
Vol balance 2250
P1 balance 50
Q2(july, aug, sep)
Vol balance 1200
P1 balance 400
for now
and if next Quarter starts i need to show the next Quarter as well.
Please help me in solving this
Hi Bhavesh,
thank you for the advice. Went through the video, and captured most of what I need, unfortunately, could not fully understand how the Previous month feature works.
Previous Month = TOTALMTD(SUM('Table'[Cost in USD]), PREVIOUSMONTH('Table'[Fiscal Month]))
Such a formula will show me the current month. How do I pull data for a full period before. In this case, July?
Thanks,
Atanas
Hi Atanas,
Try this measure for getting the previous month results:
Previous Month := CALCULATE(SUM('Table'[Cost in USD]), DATEADD('Table'[Fiscal Month],-1,MONTH))
Thanks & Regards,
Bhavesh
Hi Bhavesh, thanks, works perfectly, however, used the TotalMTD to get the data only for the month I need. What would be the case with Quarter information?
Tried using the one below, i believe its totally wrong.
TOTALQTD(SUM('Table'[Cost in USD]), DATEADD('Table'[Fiscal Month],-1,QUARTER))
Any idea regarding it?
Thanks, Atanas
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |