I am trying to use Switch True logic with dates but I am unable to find how to tell the system that I am dealing with dates using a measure.
I have 3 types of dates that I would like to convert:
Various rows are older than 01.01.2021 and I would like to convert them into 01.01.2021.
Same is true for data that are after the 31.12.2021 and I would like to convert them into 31.12.2021
I do have a lot of blank data which I would like to return with 31.12.2021
Unless I am mistaken, I need to either use an iteration formula or a measure to build the logic and I would go for iteration. I do have a date table in my model.
End date calc =
SWITCH ( TRUE(),
sumx(‘Table’[End date] <= “01.01.2021”, “01.01.2021”)) → this is where I get stucked, I tried to use DATE or “” but it didn’t work.
Can you elaborate more on the purpose of the measure you are trying to create and how you intent to use it… It reads as though you are trying to transform column values and if that is the case its more a job for Power Query however please supply a work in progress PBIX with data file.
While anonymizing my data, I realized that I only have 1 table left, so you are probably right that this is more a job for Power Query. The reason I wanted a measure was to avoid adding more data into my model.
This is a customer list with start date of the contract and end date of the contract (if any). I would like to determine the number of active days during the year, most of them will be 365 during the Fiscal Year
If the end date is blank, then the contract is still active but it can happen that the end date is before the beginning of the fiscal year or after year end.
Same is true for start date, it can be before the beginning of FY or after YE.
I now realize that if I try a Switch True Logic, then I might have some issue next year when I load new figures.
Okay so I now read your requirement as “count the number of days a contract is active for a given year” based on that being dynamic, power query is out.
Here’s what I did.
Fixed the bug in your Date table, marked your Date table as a Date table, removed the Year column from the Customer Data table and formatted the Contract end date as a date.
And created this measure
Active Days =
VAR _Dates = CALCULATETABLE( VALUES( Dates[Date] ), ALLSELECTED( Dates[Date] ))
VAR vTable =
ADDCOLUMNS(
SUMMARIZE( 'Customer data', 'Customer data'[Customer name], 'Customer data'[Type], 'Customer data'[Start date], 'Customer data'[Contract End Date] ),
"Count", COUNTROWS( FILTER( _Dates, [Date] >= [Start date] && [Date] <= COALESCE( [Contract End Date], MAXX( _Dates, [Date] ))))
)
VAR Result = SUMX( vTable, [Count] )
RETURN
Result
Thank you so much for this. It seems to work, it even captures 366 days for 2020.
On the measure you created, is there a way to only get current year? I need this measure at a future stage to determine a ratio = number of active days / 365 (or divided by 366 for leap years)
Is there any specific reason you removed the Year column? My table also include revenue by customer for this specified year and I will need it at a future state to calculate an annualized revenue for customer terminating their contract during the year.
Active Days CY =
VAR _cYear = YEAR( TODAY() )
VAR _Dates = CALCULATETABLE( VALUES( Dates[Date] ), FILTER( VALUES( Dates[Year] ), [Year] = _cYear ))
VAR vTable =
ADDCOLUMNS(
SUMMARIZE( 'Customer data', 'Customer data'[Customer name], 'Customer data'[Type], 'Customer data'[Start date], 'Customer data'[Contract End Date] ),
"Count", COUNTROWS( FILTER( _Dates, [Date] >= [Start date] && [Date] <= COALESCE( [Contract End Date], MAXX( _Dates, [Date] ))))
)
VAR Result = SUMX( vTable, [Count] )
RETURN
Result
For a ratio of the current year OR make a change to the Result variable and replace the _Dates with _cYTD for the number of days in the current year to date.
Active Days ratio CY =
VAR _cYear = YEAR( TODAY() )
VAR _cYTD = FILTER( ALL( Dates[Date], Dates[Year] ), Dates[Year] = _cYear && Dates[Date] <= TODAY())
VAR _Dates = CALCULATETABLE( VALUES( Dates[Date] ), FILTER( VALUES( Dates[Year] ), [Year] = _cYear ))
VAR Result =
DIVIDE( [Active Days CY], COUNTROWS( _Dates ))
RETURN
Result
.
Yes you don’t need it
Same goes for: “revenue by customer for this specified year…” that should be a Measure.
Try to think of it in this way, all numerical calculations are measures, calculated columns are only used to create attribures to slice and dice by (having said that - you should probaply create those attributes in Power Query or even further upstream if that’s possible).
I’m assuming you have an Excel backgroud and are relatively new to Power BI… In that case I can highly recommend the Accelerator initiative. It’s the best quick start quide to create sound PBI reports, combining not only theory and practice but also a live Q&A each round.
Indeed, I am starting with Power BI and have an Excel background. I will follow your recommendation on the accelerator.
Regarding the “revenue by customer for this specified year”, this is not a calculation, this is directly coming from my database as 1 single line. I don’t need to calculate it (otherwise i would create a measure). The only thing I would like to do is for next year to append my data to this table so I can identify current year revenue VS next year.
Hi @JayLocher, based on @Melissa’s response, it looks like your original query has been answered. Kindly tag this post as solved and create a separate post about the new issue presented to create more visibility. Thanks!
