Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
In query mode I've created a new custom column containing the period "YYYY-MM" from another date column (InvoiceDelivery Date):
Transacton period=Number.ToText(Date.Year([InvoiceDeliveryDate]))&"-"&Number.ToText(Date.Month([InvoiceDeliveryDate]))
My problem is that I've got 2018-1 as a result instead of 2018-01, 2018-2 instead of 2018-02 and so on.
Later I'm sorting my charts on this period column and it results in 2018-1, 2018-10, 2018-02, 2018-03 and so on. I want it look like 2018-01, 2018-02, 2018-03 ... instead
I've tried to user the "format" command instead but this is not accepted in the query edit mode. got the message Expression.Error: The name 'FORMAT' wasn't recognized.
TransactionPeriod = FORMAT(Lagerhyra[InvoiceDeliveryDate];"YYYY-MM")
I have to create the new period column during the query since I use the period field in a "group by" command in the same query!
Thanks in advance for all your help!
Solved! Go to Solution.
@mrg64 Please try this...
Transaction Period =Text.Combine({Date.ToText(Date.From([InvoiceDeliveryDate]), "yyyy"), "-", Date.ToText(Date.From([InvoiceDeliveryDate]), "MM")})
Proud to be a PBI Community Champion
@mrg64,
In addition to other's post, you can only use your second formula in DAX. As shown in the following example, you can create a calculated column in Power BI data view.
Regards,
Lydia
@mrg64 Please try this...
Transaction Period =Text.Combine({Date.ToText(Date.From([InvoiceDeliveryDate]), "yyyy"), "-", Date.ToText(Date.From([InvoiceDeliveryDate]), "MM")})
Proud to be a PBI Community Champion
Thanks a lot, it works perfect
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |