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.
All right, good luck.
You should actually thank @Phil_Seamark for that little beauty, until I understood GENERATE (which I came to from reading his book), that problem vexed me from almost the very first day I worked with Power BI!
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
The quick measures I referred you to are measures, not columns.
All right, good luck.
I did not manage with the solution I was following.
I used your code and it works !!!!
It just look so scary I was afraid to even read it 🙂
I have code below. It uses SUMX at the end. Total Amount 2 = VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#"))) VAR tmpBilling = ADDCOLUMNS('Demand',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")), "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#"))) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpBilling, SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear]) ), [MonthYear] >= [MonthYearBegin] && [MonthYear] <= [MonthYearEnd] ), "Customer",[Customer], "Year",[Year], "Month",[Month], "Amount",[Amount] ) RETURN SUMX(tmpTable,[Amount])
You should actually thank @Phil_Seamark for that little beauty, until I understood GENERATE (which I came to from reading his book), that problem vexed me from almost the very first day I worked with Power BI!
One thing I don't understand.
In the billing example pbix
There is a Calendar Table with Date column. It was showing me chronological order in the bar graph. When I removed it and put back again to the chronology changed (I think I was playing with my data sample of 5 ).
Is there anything beside Date and Date Hierarchy to play around ?
Like in my ultimate version I got data for 2017,2018,2019,2020 but by choosing Hierarchy I can only see Jan-Dec
Not sure whether it is 2019 which has all the amounts really or all combined by monthly basis.
I would like to see continum of 2017-2020
any idea ?
Thanks
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |