20/11/24 as Nov-24 but still as date data type

[jutu]

HI. I have date format column (say 20/11/24). I changed it as Nov-24 but it changes the column into a text data type? I formatted it to achieve Nov-24 but it becomes a text data type and tried different things but running out of ideas if you can help please. Thank you

 

[AliGW]

I used formatting to do the same and the dates are still being read as dates:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A​	B​	C​	D​
1​	Nov 24​
2​	Dec 24​		31​	=DATEDIF(A1,A2,"d")
3​			31​	=A2-A1

Sheet: Sheet1

Attach a workbook so that we can get to the bottom of it.

 

[jutu]

Apologies I may haven't put the question in the right section. This is not in excel but in power bi

 

[AliGW]

This sectopn is about PowerB!, PowerQuery and PowerPivot - in your opening post, you did not specify any of the three.

Please provide a workbook - we cannot manipulate screenshots (they are of practically no use whatsoever). PowerQuery in Excel should behave in the same way as PowerBI, so you can use that for your example.

 

[Debaser]

Why are you trying to 'format' it in PQ? (you can't, you just end up converting it to text). The formatting should be done in the data model and/or visuals, not in PQ.

 

[jutu]

Thanks Debaser. I think few questions here actually. Sorry.

I did try formating it from the PBI desktop and don't quite get MM-YY but am ok with the full month name and year format many thanks.
However, the idea is to also group it so sum all the letters for that month/year/quarter. The other problem that i have is that the total, it doesn't show. I used the below dax for the P1 letters field:
Count of P1 ClassLetters = CALCULATE(COUNTROWS('Semantic DimCommunicationType'),'Semantic DimCommunicationType'[CC Class_P1&P2] = "P1")

As you can see below, for the total I tried to cancel the totals for "Specific column" but still gives me the total. I require it but, for now, was going to hide the totals which doesn't work.

 

[Debaser]

If you want them grouped, you'll have to convert the dates to a common day of the month - usually 1st or last. You might also use a date table and then use separate fields for year, month.
I'm not clear on what you want for the totals.

 

[jutu]

Hi Debaser. The total at the bottom shows 3, same as each row so i guess it's something to do with the filter/row context but not sure.

 

[jutu]

Hi Debaser. I found what I was doing wrong thanks to you. I was trying to format it from the PQ and could never get it right from there but changed the format in the model as a calculated column and it all worked just fine. I suppose it's to do the row context? Thank you very much. It was a good lesson

LetterSentDate.Month&Year = FORMAT('Semantic FactCommunications'[LetterSentDate], "yyyy-MMM")

 

[jutu]

Only thing i can't sort out yet is the totals. The below, it should be 39 in total, but it shows 3 which i don't quite get. I know totals can be a headache and if you can help please.

I am using the 2 dax below for each, P1 and P2 letters. Each field are 39 letters in total, but showing 3:
RowCount of P2 ClassLetters = CALCULATE(COUNTROWS('Semantic DimCommunicationType'),'Semantic DimCommunicationType'[CC Class_P1&P2] = "P2")

RowCount of P1 ClassLetters = CALCULATE(COUNTROWS('Semantic DimCommunicationType'),'Semantic DimCommunicationType'[CC Class_P1&P2] = "P1")

 

[Debaser]

Hi Debaser. I found what I was doing wrong thanks to you. I was trying to format it from the PQ and could never get it right from there but changed the format in the model as a calculated column and it all worked just fine. I suppose it's to do the row context? Thank you very much. It was a good lesson

LetterSentDate.Month&Year = FORMAT('Semantic FactCommunications'[LetterSentDate], "yyyy-MMM")

No, it's nothing to do with context. What you have done there is exactly the same as what you had before - i.e. a text representation of a date.

 

[Debaser]

Regarding the totals, I can't say without seeing the model, but the fact that you are getting the same value for every row looks pretty odd to me, especially considering the volume of letters sent. I suspect you either have no relationships set up or they are incorrect. If not, you will need a measure that does a different calculation when there isn't a filter on send date, the adds up all the P1 and P2 measure values using something like SUMX(VALUES([Letters send date]),[P1 measure name])