DATESBETWEEN or DATESMTD not working
I am struggling to figure out how to get a set of dates for the latest, filtered month of dates in my model. I have two measures which accurately show the range of dates I am looking to run my calculation by, but when I use either of the two functions below, I still get a month's worth of dates in the latest month of my Dates Table: // Option 1 - Dates Between _mtd = DATESBETWEEN(DateTable[Date], [StartOfMonth], [EndOfMonth]) // Option 2 - Dates MTD _mtd = DATESMTD(DateTable[Date]) // Option 3 - Dates MTD with a filtered start _mtd = CALCULATETABLE(DATESMTD(DateTable[Date]), DateTable[Date] = [StartOfMonth]) I'm not sure what I'm doing wrong, but hoping that this is something that's acheivable. I'm ultimately running calculations for current month, month/month, and year/year.
Custom Month to date
Hello all, Our fiscal months are not based on calendar months but ends on the Saturday of every month. Therefore the our fiscal month starts on different day every month. I have the dax formula to calculate the last saturday of the month but I am not sure how to implement it to use in my slicer Here's the measure that give me the last saturday of the month customEOM = VAR dateVal = [Date] RETURN if ( dateVal > DATE ( '2018, 12, 31'), if ( dateVal > if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) = 7, EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ) - WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ) ), EOMONTH ( dateVal, 0 ) )
MTD calculation on the 1st of each month
I have multiple visualizations that show me case fill (ship qty/order qty) on my report. Many of them are filtered in the filter pane to show case fill for orders only in the current month, therefore creating a MTD metric visualization. I relaized today on the 1st of the month, no data was showing. Our data is refreshed in the monring to update the data that was put in yesterday. Since today is 2/1 but data is only loaded through 1/31, my MTD visualizations are empty. I was trying to create a measure that would give me case fill for the last 30 days when it's the first of the month, and normal MTD case fill any other day of the month. This is what I came up with, but when I plut it inot my visuals no data is generating. MTD CF = IF(TODAY()=STARTOFMONTH('Calendar Table'[Date]), CALCULATE('Case Fill Data'[Case Fill], DATESBETWEEN('Calendar Table'[Date], PREVIOUSDAY('Calendar Table'[Date]),TODAY()-30)), TOTALMTD('Case Fill Data'[Case Fill],'Calendar Table'[Date]))
Calculate the previous month value with the same date range
Hello, I have problem with defining dax for calculating the sum of previous month, the conditions: - This month is February and the data is only available until 19 February, I have calculated this month ongoing sum which is from 1-19 February as selected month measure. - I want to calculate the same period in previous month but with the same date range as I have now, i.e. sum of sales 1-19 February vs. sum of 1-19 January. Ive tried to use this formula (shown below), but it calculates the entire sum of sales in January instead of 1-19 January only. What step do I miss? Really need your help, thanks in advance guys 🙂How to calculate QTD and YTD based on MTD
Hello, I have a table with MTD value calculated everyday by ETL team. as a report developer, how to calcuate QTD and YTD by using the MTD value? There is no daily value in the fact table. Date MTD QTD YTD 2022-01-01 5 2022-01-02 8 ..... 2022-01-30 100 2022-01-31 110 2022-02-01 2 2022-02-02 5 .... 2022-02-27 70 2022-02-28 76 ..... 2022-03-01 7 2022-03-02 11 ....MTD sum does not give total till date
I have sales and target colums in sales report. i wanted to make target month to date. for sales i used the simple formula: MTD Sales= TOTALMTD([NetTotal] ,SalesmanTarget[TargetDay]) NetTotal is the sum of sales & TargetDay is the date column the result is correct I made another formula MTD Target= TOTALMTD([Actual Target],SalesmanTarget[TargetDay]) where Actual Target= SUM(SalesmanTarget[TargetAmount]) and the results are as follows MTD sales and Actual Target are correct, but the MTD target is not. MTD Target should be the target assigned to the sales team from start of the current month till date. but as you see i am not reaching the correct sum for MTD Target. is there a mistake in my formulas? I appreciate your help and support
