Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |