Created
June 28, 2018 15:00
-
-
Save SchreiberLars/a3f87baad63392082d97cb006ec24eab to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
fn = (MyDate as date, StartMonat as text) => | |
let | |
Startmonatsnummer = | |
List.PositionOf( | |
{"januar", "februar","märz","april","mai","juni", "juli", "august", "september", "oktober", "november", "dezember"}, | |
Text.Lower(StartMonat) | |
) + 1, | |
Offset = Startmonatsnummer -1, | |
AtuellerKalendermonat = Date.Month(MyDate), | |
Geschaeftshalbjahr = | |
if AtuellerKalendermonat < Startmonatsnummer then | |
"GHJ"&Number.ToText(Number.RoundUp((13 - Startmonatsnummer + AtuellerKalendermonat) / 6, 0)) &"_"& Number.ToText(Date.Year(MyDate)-1) | |
else | |
"GHJ"& Number.ToText(Number.RoundUp((AtuellerKalendermonat - Offset) / 6, 0)) &"_"& Number.ToText(Date.Year(MyDate)) | |
in | |
Geschaeftshalbjahr, | |
fnType = type function( | |
MyDate as (type date meta [Documentation.LongDescription="Hier wird das zu prüfende Datum (meist aus einer Datumsspalte) übergeben."]), | |
Startmonat as (type text meta | |
[ | |
Documentation.LongDescription="Hier wird der Monat als Text eingegeben, mit dem das Geschäftsjahr beginnt", | |
Documentation.AllowedValues = {"Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"} | |
] | |
) | |
) as text meta [ | |
Documentation.Name = "fnGeschaeftsHalbjahrUndJahr", | |
Documentation.LongDescription = | |
"Gibt die Nummer des aktuellen Geschäftshalbjahres und des Geschäftsjahres wieder. Dies ist sinnvoll bei von Kalenderjahr abweichenden Geschäftsjahren", | |
Documentation.Author = "Lars Schreiber, www.ssbi-blog.de" | |
] | |
in | |
Value.ReplaceType(fn, fnType) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment