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'd like to show cumulative totals for a period based on certain filters. I am able to do it with SQL but having trouble to do the same using Power BI.
I've two tables - actual data (A) and (B) calendar table (with start and end dates). So, with SQL i just joined these tables using the following join condition; this basically duplicates data for every unit each month which i can then use for aggregations etc in Power BI
SELECT * FROM A
LEFT JOIN B
ON A.InstallDate <= B.EndDate
WHERE Status IN ('Live','Pending Disconnect')
In Power BI, i tried to used power query to merge these tables but cannot use the join condition the same way. I have also tried DAX Calculate and Filter but had no luck
Attached is sample data and expected output. I could not attach sexcel file, so included a screenshot
I am hoping to get some guidance here
Thank You
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Excatly what i have been looking for. Thank You!!
I am now trying to understand the steps you have in power query
Lot to learn for me
Hi,
You may download my PBI file from here.
Hope this helps.
Excatly what i have been looking for. Thank You!!
I am now trying to understand the steps you have in power query
Lot to learn for me
You are welcome.
@Ashish_Mathur - If i may ask you another question about the same
i am trying to create a forecast for the previous and current years based on the distinct sample data and some other columns
A = SUM(Amount) -- for id's with pending disconnect in the Year
B = SUM(Amount) -- for id's that are live
C = SUM(Amount) -- for id's that have been disconnected in the year
D = SUM(UserCount)
ForecastA = ((A+B)-C)/D
Something like this.. So, i have created measures for A,B,C to verify the data and thought i would be able to use these measures in final calculation but had no luck. Can we not use measures along with columns from other related tables (d is from different table)?
I was able to use Variables and do the calculations but i have to rewrite the same piece of code for few different measures i.e., (A+B)-C calculation would be same but D column would be different.
Is there any simpler way to do this?
Thanks
Measure = VAR AmountLive = CALCULATE(SUM(LD[Amount]),FILTER(LD,LD[Status]="Live")) VAR AmountOnOrder = CALCULATE(SUM(LD[Amount]),FILTER(LD,AND(LD[Status]="On Order",LD[PIyear] = Year(Now())))) VAR AmountPD = CALCULATE(SUM(LD[Amount]),FILTER(LD,AND(LD[Status]="Pend Disc",LD[PDYear] = Year(Now())))) VAR TotalAmt = SUM((AmountLive+AmountOnOrder)-AmountPD) VAR TotalHC = SUM(BB[Users]) RETURN DIVIDE(TotalAmt, TotalHC)
Hi,
I just do not understand your question. Show a dataset, explain the business problem and show me the expected result.
Hi @Anonymous,
Not sure if you tried creating a calendar table?! If not, createa calendar table:
DAX:
Date = ADDCOLUMNS ( CALENDAR (DATE(2017,1,1), DATE(2020,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
Then create a relationship between calendar date field and your tables based on their date fields (install date and end date).
Then try creating a calculated column or measure to sum amounts.
something like this: calculate(sum('table name' [amount]), 'table name'[status] = "Live'', 'table name'[status] = ''Pending Disconnect'')
But use date field from the calendar table in your table matrix.
Hope it helps.
Regards
Abduvali
Thanks for your reply. I've tried something similar and it did not work.
I've tried it again using the sample data and the DAX function you mentioned. i got no value. I tried the following
SampleMeasure = Calculate(Sum('Sample Data'[Amount]), FILTER(ALL('Sample Data'), 'Sample Data'[Install Date] <= MAX(DateCalendar[Date])))
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |