Week count after&before New Year
Hello, I am a newbie who's using Power BI for the first time, and find myself struggling over Week splitting when there is a year transition. Indeed, I have a TIME table, whose columns are calculated like this: By default, Week 1 begins the first Monday of the new year (Spanish calendar, weeks are Mon-Sun). The problem is that with the DAX configuration above, week 52 is absorbed by January (which is incorrect, it should fall in December): Does anyone who if/how can Power BI set a standard formula in order to consider Week 1 the one starting on January 1st, and week 52 until 31st December? Thanks a lot for any hint regarding this topic (I haven't been able to find a related tutorial). Regards, TeresaCreation of DAX measures instead of calculated columns for calculating time speed % categories
Hello All, I am trying to calculate through only DAX Measures the quantity of tickets being responded in 4 categories of speed (Difference between the timestamp of ticket creation minus the timestamp of ticket_start_to_resolve) : <1 minute, 1 to 5 minutes, 5 to 60 minutes, >1 Hour Right now it is calculated in the following steps: 1.- Calculated the difference of timestamps from the two factTable timestamps previously described and named that column as "responseSpeed" -> Performed in PowerQuery 2.- Creation of 4 calculated conditional columns from the previous "responseSpeed" calculation and marked each row as 1 if it is categorized as <1 minute, 1 to 5 minutes, 5 to 60 minutes or >1 Hour ->Perfomed too in Power Query 3.- Calculation of measures for totals by category with additional specific filters related to the business thanks to CALCULATE function and FILTERS from other specific columns. ->Those measures are calculated in DAX and they are calculated like CALCULATED(SUM(), filtered(all(columns, columns="value") of the previous calculated columns in Power Query and filtered. 4.- Finally, those 4 measures in DAX are divided by the total quantity of tickets created so that We have the calculation for every % from each category. ->Performed in DAX and examples like: % <1min = [<1min] / [Total numbers of tickets] So, what is the deal? I am trying to integrate steps 1.- and 2.- in DAX Measures too in order to free space for the .pbix size and later on the model in the PowerBI service. Calculated columns are not considered as the space occupied is higher even than the steps performed What I have tried until now 🙂 1.- Perform a measurement called SpeedResponse SpeedResponse = SUMX(factTable, 'factTable[ticket_response_timestamp] - 'factTable[ticket_creation_timestamp]) 2.- (integration of 2.- and 3.- former steps into one Measurement). <1 min_total = //I know the following is not working, but in that way you will see the idea behind calculating the total of rows (tickets) with specific speed so that I can count them and then filter that total ammount with the additional filters later. var _lessThan1Minute = COUNTX(factTable, CALCULATE([SpeedResponse], IF(AND(HOUR([SpeedResponse])=0, MINUTE([SpeedResponse])<1),1,0)) ) var _lessthan1MinuteFiltered = CALCULATE(_lessThan1Minute), FILTER(ALL(factTable[ticketOrigin]), ALL(factTable[ticketOrigin]="outsourcing") return _lessthan1MinuteFiltered Thank you so much in advance community!
